﻿using SMALLERP.ComClass;
using SMALLERP.DataClass;
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using Microsoft.VisualBasic;
using Newtonsoft.Json;
using RestSharp;
using XJLSC.ComClass;
using System.Data.SqlClient;

namespace XJLSC.Sy
{
    public partial class SendMails : Form
    {
        private SqlDataReader sdr1;
        private readonly DataBase db = new DataBase();
        public delegate void Cdgzzx(object sender, EventArgs e);
        //定义委托对象
        public Cdgzzx cdgzzx;
        private readonly object DESencrypt;

        public SendMails()
        {
            InitializeComponent();
            Control.CheckForIllegalCrossThreadCalls = false;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DateTime dt = DateTime.Now;
            DateTime dt1 = dt.AddDays(-1);
            string zr = dt1.ToShortDateString();
            String filename = "汽空"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            string savePath = string.Empty;
            string savePath1 = string.Empty;
            savePath = "D:\\report\\" + filename;
            savePath1 = "D:\\report\\汽空temp.xlsx";
            //需要添加 Microsoft.Office.Interop.Excel引用 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            if (null == app)
            {
                MessageBox.Show("缺少Excel组件！");
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            string strSql = "";

            strSql = "SELECT B.QYBH 区域编号,B.QYMC 区域名称,D.MD004 标准人数,CAST(ISNULL(CQGS,0) AS NUMERIC(16,2)) 出勤工时,(SELECT SUM(CAST(ISNULL(YXGS,0) AS NUMERIC(16,2))) FROM V_GQYXGS T WHERE TE023=CONVERT(CHAR(8),GETDATE()-2,112) AND B.QYBH=T.TA006) 总有效工时,(SELECT SUM(CAST(ISNULL(S.FH*7.5,0) AS NUMERIC(16,2))) FROM V_FHMX S WHERE B.QYBH=S.GZZX) 区域总负荷,B.SCXLB 生产线类别,B.FH*7.5 总负荷,B.YYQFH*7.5 已逾期负荷,B.YZNFH*7.5 一周内负荷,B.EZNFH*7.5 二周内负荷,B.EZHFH*7.5 二周后负荷,CAST(ISNULL(YXGS,0) AS NUMERIC(16,2)) 有效工时,ISNULL(YYQ,0.00) 已逾期,ISNULL(YZN,0.00) 一周内,ISNULL(EZN,0.00) 二周内,ISNULL(EZH,0.00) 二周后";
            strSql += " FROM V_FHHZ B LEFT JOIN CMSMD D ON B.QYBH = D.MD001 LEFT JOIN CMSME E ON D.MD015 = E.ME001 LEFT JOIN (SELECT * FROM V_GQCQGS WHERE VY002 = CONVERT(CHAR(8), GETDATE() - 2, 112)) A ON A.ME001 = E.ME001";
            strSql += " LEFT JOIN (SELECT * FROM V_GQYXGS WHERE TE023 = CONVERT(CHAR(8), GETDATE() - 2, 112)) C ON C.TA006 = B.QYBH AND B.SCXLB = C.SCXLB LEFT JOIN V_YXGSMX Q ON B.QYBH = Q.TA006 AND B.SCXLB = Q.UDF02 WHERE E.ME001 LIKE '0302%' ORDER BY E.ME001,B.SCXLB";

            try
            {
                //dataGridView3.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                DataTable jkyqwbgdt = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                worksheet.Name = "各区域人工投入产出(生产)";
                for (int i = 0; i < jkyqwbgdt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = jkyqwbgdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < jkyqwbgdt.Rows.Count; r++)
                {
                    for (int i = 0; i < jkyqwbgdt.Columns.Count; i++)
                    {
                        if (i <= 3 && r>0 && jkyqwbgdt.Rows[r][0].ToString() == jkyqwbgdt.Rows[r - 1][0].ToString())
                        {
                            worksheet.Cells[r + 2, i + 1] = "";
                        }
                        else
                        {
                            worksheet.Cells[r + 2, i + 1] = jkyqwbgdt.Rows[r][i].ToString();
                        }
                    }
                }

            }
            catch
            {
            }
            strSql = "SELECT SUBSTRING(MOCTY.CREATE_DATE,1,8) 派工日期,ISNULL(TD013,'无订单号') 成品交货日期,TY001 单别,TY002 单号,A.TA006 品号,A.TA034 品名,A.TA035 规格,CEILING(TY004) 计划数量,CEILING(TY006) 完成数量,TY003 计划完成日期,B.TA007 区域,B.TA024 工艺说明,TY009 生产线,TY020 作业员工,MV002 员工姓名 FROM MOCTY LEFT JOIN MOCTA A ON TY001=A.TA001 AND TY002=A.TA002 LEFT JOIN CMSMV ON TY020=MV001 LEFT JOIN SFCTA B ON A.TA001=B.TA001 AND A.TA002=B.TA002 AND TY019=B.TA003 LEFT JOIN COPTD ON A.TA026=TD001 AND A.TA027=TD002 AND A.TA028=TD003 WHERE TY001 LIKE '8%' AND TY003<>'' AND TY003<=CONVERT(CHAR(8),GETDATE()-2,112) AND A.TA011<>'Y' AND A.TA011<>'y' AND TY020<>'' AND TY004-TY006>0 AND TY018<>'Y' ORDER BY B.TA006,TY003";
            try
            {
                //dataGridView2.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                DataTable yqwbgdt= db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[2];
                worksheet.Name = "计划逾期未报工(生产)";
                for (int i = 0; i < yqwbgdt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = yqwbgdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqwbgdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqwbgdt.Columns.Count; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = yqwbgdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {
            }

            strSql = "SELECT SUBSTRING(MOCTY.CREATE_DATE,1,8) 派工日期,ISNULL(TD013,'') 成品发货日期,TY001 单别,TY002 单号,A.TA006 品号,A.TA034 品名,A.TA035 规格,B.TA024 工艺说明,CEILING(TY004) 计划数量,CEILING(TY006) 完成数量,CEILING(TY004-TY006) 未完成数量,TY003 计划完成日期,TY009 生产线,TY020 作业员工,MV002 员工姓名,B.TA007 区域 FROM MOCTY LEFT JOIN MOCTA A ON TY001=A.TA001 AND TY002=A.TA002 LEFT JOIN CMSMV ON TY020=MV001 LEFT JOIN SFCTA B ON A.TA001=B.TA001 AND A.TA002=B.TA002 AND TY019=B.TA003 LEFT JOIN COPTD ON A.TA026=TD001 AND A.TA027=TD002 AND A.TA028=TD003 WHERE TY001 LIKE '8%' AND TY003<>'' AND TY003=CONVERT(CHAR(8),GETDATE()-2,112) AND A.TA011<>'Y' AND A.TA011<>'y' AND TY020<>'' ORDER BY B.TA006,TY003";
            try
            {
                //dataGridView2.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                DataTable jhdcdt = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
              Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[3];
               worksheet1.Name = "计划达成(生产)";
                for (int i = 0; i < jhdcdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = jhdcdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < jhdcdt.Rows.Count; r++)
                {
                    for (int i = 0; i < jhdcdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = jhdcdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {
            
            }

            strSql = "SELECT * FROM V_QYPG WHERE 区域编号 LIKE '6%' AND 区域编号<>'6700' AND 区域编号<>'6800' ORDER BY 区域编号,类型";
            try
            {
                DataTable sctjdt = db.GetDataSet1(strSql, "MOCUY").Tables["MOCUY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[4];
                worksheet1.Name = "各区域需优先派工明细(生产)";
                for (int i = 0; i < sctjdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = sctjdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < sctjdt.Rows.Count; r++)
                {
                    for (int i = 0; i < sctjdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = sctjdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {
                
            }

            strSql = "SELECT SFCTA.TA007 区域,CONVERT(CHAR(8),DATEADD(DAY,0,TE023),112) 生产日期,TD003 报工日期,ISNULL(SFCTD.UDF01,'') 班次,RTRIM(ISNULL(A.UDF09,0)) 生产线,RTRIM(TE004) 员工编号,RTRIM(MV002) 员工姓名,(CASE WHEN A.UDF61=0 THEN 1 ELSE CEILING(A.UDF61) END) AS 员工个数,(CASE WHEN A.UDF62=0 THEN 1 ELSE CAST(A.UDF62 AS NUMERIC(16,6)) END) AS 数量比例,TE006 工单单别,TE007 工单单号,RTRIM(TE017) 品号,RTRIM(TE018) 品名,RTRIM(TE019) 规格,ISNULL(SUBSTRING(SFCTA.TA024,1,255),A.UDF01) 工艺说明,RTRIM(A.TE015) 备注,CEILING(CASE WHEN TE001='D420' THEN A.UDF56 ELSE CAST(TE011 AS NUMERIC(16,1)) END) 数量,CEILING(A.UDF51) 料废,CEILING(A.UDF52) 工废,CEILING(A.UDF53) 不良,CEILING((CASE WHEN SFCTA.UDF57=1 THEN 0 ELSE SFCTA.UDF57 END)*TE011) 加工工时,CAST((CASE WHEN (SFCTA.UDF57=1 OR SFCTA.UDF57=0) AND SFCTA.UDF55>0 THEN TE011/SFCTA.UDF55 ELSE 0 END) AS NUMERIC(16,2)) 生产班数,CAST(SFCTA.UDF57 AS NUMERIC(16,2)) AS 节拍,CEILING(SFCTA.UDF55) 定额,TE001 单别,TE002 单号,TE003 序号 FROM SFCTE A LEFT JOIN SFCTD ON TE001=TD001 AND TE002=TD002 LEFT JOIN SFCTA ON TE006=TA001 AND TE007=TA002 AND TE008=TA003 LEFT JOIN CMSMV ON TE004=MV001 WHERE TD003=CONVERT(CHAR(8),GETDATE()-1,112) AND TE006 LIKE '8%' AND CEILING(A.UDF51+A.UDF52+A.UDF53)>0 ORDER BY SFCTA.TA006,A.TE011,A.TE012,A.TE023,TD003,A.TE003,A.TE015,A.UDF06";
            try
            {
                DataTable sctjdt = db.GetDataSet1(strSql, "MOCUZ").Tables["MOCUZ"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[5];
                worksheet1.Name = "生产不良及报废明细(生产)";
                for (int i = 0; i < sctjdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = sctjdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < sctjdt.Rows.Count; r++)
                {
                    for (int i = 0; i < sctjdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = sctjdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT B.TA007 区域,CONVERT(CHAR(8),DATEADD(DAY,0,TE023),112) 生产日期,E.TD003 报工日期,ISNULL(E.UDF01,'') 班次,RTRIM(ISNULL(A.UDF09,0)) 生产线,ISNULL(dbo.fn_strSum11(A.TE017),'') 设备,RTRIM(TE004) 员工编号,RTRIM(MV002) 员工姓名,A.UDF58 出勤工时,ISNULL(D.TD013,'') 预计交货日,CAST((CASE WHEN DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))<0 THEN (CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011  / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54=0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END) ELSE 0 END)*7.5 AS NUMERIC(16,2)) 已逾期,CAST((CASE WHEN DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))<7 AND DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))>=0 THEN (CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011  / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54=0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END) ELSE 0 END)*7.5 AS NUMERIC(16,2)) 一周内,CAST((CASE WHEN DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))<=14 AND DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))>=7 THEN (CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011  / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54=0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END) ELSE 0 END)*7.5 AS NUMERIC(16,2)) 二周内,(CASE WHEN A.UDF61=0 THEN 1 ELSE CEILING(A.UDF61) END) AS 员工个数,(CASE WHEN A.UDF62=0 THEN 1 ELSE CAST(A.UDF62 AS NUMERIC(16,6)) END) AS 数量比例,TE006 工单单别,TE007 工单单号,RTRIM(TE017) 品号,RTRIM(TE018) 品名,RTRIM(TE019) 规格,ISNULL(SUBSTRING(B.TA024,1,255),A.UDF01) 工艺说明,RTRIM(A.TE015) 备注,CEILING(CASE WHEN TE001='D420' THEN A.UDF56 ELSE CAST(TE011 AS NUMERIC(16,1)) END) 数量,CEILING(A.UDF51) 料废,CEILING(A.UDF52) 工废,CEILING(A.UDF53) 不良,CEILING((CASE WHEN B.UDF57=1 THEN 0 ELSE B.UDF57 END)*TE011) 加工工时,CAST((CASE WHEN (B.UDF57=1 OR B.UDF57=0) AND B.UDF55>0 THEN TE011/B.UDF55 ELSE 0 END) AS NUMERIC(16,2)) 生产班数,CAST(B.UDF57 AS NUMERIC(16,2)) AS 节拍,CEILING(B.UDF55) 定额,CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54 = 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END * 7.5 有效工时,TE001 单别,TE002 单号,TE003 序号  FROM SFCTE A LEFT JOIN SFCTD E ON TE001=E.TD001 AND TE002=E.TD002 LEFT JOIN SFCTA B ON TE006=B.TA001 AND TE007=B.TA002 AND TE008=TA003 LEFT JOIN CMSMV ON TE004=MV001 LEFT JOIN MOCTA C ON TE006=C.TA001 AND TE007=C.TA002 LEFT JOIN COPTD D ON C.TA026=D.TD001 AND C.TA027=D.TD002 AND C.TA028=D.TD003 ";
            strSql += "WHERE TE023 = CONVERT(CHAR(8), GETDATE() - 2, 112) AND TE006 LIKE '8%' ORDER BY B.TA006,A.TE011,A.TE012,A.TE023,E.TD003,A.TE003,A.TE015,A.UDF06";
            try
            {
                DataTable scbgdt = db.GetDataSet1(strSql, "MOCTY").Tables["MOCTY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[6];
                worksheet1.Name = "报工明细(生产)";
                for (int i = 0; i < scbgdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = scbgdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < scbgdt.Rows.Count; r++)
                {
                    for (int i = 0; i < scbgdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = scbgdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

           strSql = "SELECT TC003 派工日期,ISNULL(TD013,'无订单号') 成品发货日期,TE011 工单单别,TE012 工单单号,TA006 产品图编,TA034 品名,CEILING(YCSL+DDSL) 订单数量,CEILING(YJLL) 预计领料,CEILING(MOCTC.UDF51) AS 派工数量,TE004 材料品号,MB002 材料品名,MB003 材料规格,CAST((CASE WHEN TA015=0 THEN 0 ELSE TB004/TA015 END) AS NUMERIC(16,4)) AS 单重,CAST((CASE WHEN TA015=0 THEN 0 ELSE TB004/TA015 END)*MOCTC.UDF51 AS NUMERIC(16,4)) AS 应发重量,CAST(TE005 AS NUMERIC(16,4)) 实发重量,TE006 单位,TE010 批号,TE019 是否审核 FROM MOCTE LEFT JOIN MOCTC ON TE001=TC001 AND TE002=TC002 LEFT JOIN MOCTB ON TE011=TB001 AND TE012=TB002 AND TE004=TB003 LEFT JOIN INVMB ON TE004=MB001 LEFT JOIN MOCTA ON TE011=TA001 AND TE012=TA002 LEFT JOIN V_WLPH ON TA006=TD004 LEFT JOIN COPTD ON TA026=TD001 AND TA027=TD002 AND TA028=TD003 WHERE (TC003>=CONVERT(CHAR(8),GETDATE()-5,112) OR TC009='N') AND TE011 LIKE '8%' AND (TE004 LIKE '3%' OR TE004 LIKE 'XC%') ORDER BY TC003 DESC";
            try
            {
                DataTable clckdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[7];
                worksheet1.Name = "材料出库明细(财务)";
                for (int i = 0; i < clckdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = clckdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < clckdt.Rows.Count; r++)
                {
                    for (int i = 0; i < clckdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = clckdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT ML001 品号,MB002 品名,MB003 规格,ML004 批号,MB004 单位,RTRIM(ML009) 最近入库日,RTRIM(ML010) 最近出库日,CAST((SELECT SUM(LA011) FROM INVLA WHERE LA001=ML001 AND LA016=ML004 AND LA005=1 AND LA009=ML002) AS NUMERIC(16,2)) AS 入库数量,ISNULL(CAST((SELECT SUM(LA011) FROM INVLA WHERE LA001=ML001 AND LA016=ML004 AND LA005=-1 AND LA009=ML002 AND (LA006 LIKE '5%' OR LA006 LIKE '12%')) AS NUMERIC(16,2)),0) AS 正常出库数量,ISNULL(CAST((SELECT SUM(LA011) FROM INVLA WHERE LA001=ML001 AND LA016=ML004 AND LA005=-1 AND LA009=ML002 AND (LA006 NOT LIKE '5%' AND LA006 NOT LIKE '12%')) AS NUMERIC(16,2)),0) AS 异常出库数量,CAST(ML005 AS NUMERIC(16,2)) 当前库存 FROM INVML LEFT JOIN INVMB ON ML001=MB001 WHERE MB005='112' AND ML009<=CONVERT(CHAR(8),GETDATE()-15,112) AND ML005>0 AND ML002<>'51100' ORDER BY ML009 DESC,ML001";
            try
            {
                DataTable clhydt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[8];
                worksheet1.Name = "15天前进货材料耗用情况(财务)";
                for (int i = 0; i < clhydt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = clhydt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < clhydt.Rows.Count; r++)
                {
                    for (int i = 0; i < clhydt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = clhydt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT *,DATEDIFF(DAY,CASE WHEN 下单日期='无' THEN GETDATE() ELSE 下单日期 END,GETDATE()) 已下单天数 FROM (SELECT MQ002 订单类型,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量,ISNULL(CJZZ,0) 车间在制 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN V_INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 LEFT JOIN V_CJZZ ON TD004=ZZPH WHERE TD016='N' AND TD021='Y' AND TD013<CONVERT(CHAR(8),GETDATE(),112) AND MB005='104' AND TD001<>'2205' AND TD001<>'2201' AND CEILING(TD008-TD009)-CEILING(MB064)>0";
            strSql += " UNION ALL SELECT '预测',(SELECT ISNULL(MIN(TC003), '无') FROM COPTD LEFT JOIN COPTC ON TD001 = TC001 AND TD002 = TC002 WHERE TD016 = 'N' AND TD004 = MF003 AND TD021 = 'Y') 下单日期,'2205',MF001 单号, MF002, MF003, MF004, MF005, MF006, CEILING(MF008)订单数, CEILING(MF009) 已交数, CEILING(MF008 - MF009) 未交数, CEILING(MB064) 库存数量, ISNULL(CJZZ, 0) 车间在制 FROM COPMF LEFT JOIN COPME ON MF001 = ME001 LEFT JOIN V_INVMB ON MF003 = MB001 LEFT JOIN V_CJZZ ON MF004 = ZZPH WHERE ME008 = 'Y' AND MF006 < CONVERT(CHAR(8), GETDATE(), 112) AND MB005 = '104' AND CEILING(MF008 - MF009) - CEILING(MB064) > 0 AND MF003 NOT LIKE 'GSX%' AND MF003 NOT LIKE 'ZG%' AND MF003 NOT LIKE 'SO%' AND MF003 NOT LIKE 'OK%') A ORDER BY 品号, 下单日期, 预计交货日 DESC";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[9];
                worksheet1.Name = "逾期未入库产品清单(事业部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }           
            }
            catch
            {

            }

            strSql = "SELECT * FROM (SELECT MQ002 订单类型,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN V_INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 WHERE TD016='N' AND TD021='Y' AND TD013<CONVERT(CHAR(8),GETDATE(),112) AND MB005='104' AND TD001<>'2205' AND CEILING(TD008-TD009)-CEILING(MB064)<=0";
            strSql += "UNION ALL SELECT '预测',(SELECT ISNULL(MIN(TC003), '无') FROM COPTD LEFT JOIN COPTC ON TD001 = TC001 AND TD002 = TC002 WHERE TD016 = 'N' AND TD004 = MF003 AND TD021 = 'Y') 下单日期,'2205',MF001 单号, MF002, MF003, MF004, MF005, MF006, CEILING(MF008)订单数, CEILING(MF009) 已交数, CEILING(MF008 - MF009) 未交数, CEILING(MB064) 库存数量 FROM COPMF LEFT JOIN COPME ON MF001 = ME001 LEFT JOIN V_INVMB ON MF003 = MB001 WHERE ME008 = 'Y' AND MF006 < CONVERT(CHAR(8), GETDATE(), 112) AND MB005 = '104' AND CEILING(MF008 - MF009)-CEILING(MB064)<=0 AND MF003 NOT LIKE 'GSX%' AND MF003 NOT LIKE 'ZG%' AND MF003 NOT LIKE 'SO%' AND MF003 NOT LIKE 'OK%') A ORDER BY 品号,预计交货日 DESC";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[10];
                worksheet1.Name = "逾期已入库未发货清单(销售)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT PH 品号,MB002,MB003,CEILING(SUM(KC)) 库存数,MIN(XDRQ) 下单日期,CASE WHEN MIN(XDRQ)='未下单' THEN 0 ELSE DATEDIFF(DAY,MIN(XDRQ),GETDATE()) END 已下单天数,SUM(YQWJ) 逾期未交货,SUM(YZN) 一周内交货,SUM(EZN) 一周到二周交货,(CEILING(SUM(KC))-SUM(YQWJ)-SUM(YZN)-SUM(EZN))*(-1) AS 欠数 FROM V_DDHZ LEFT JOIN INVMB ON PH=MB001 WHERE MB005='104' GROUP BY PH,MB002,MB003 HAVING (CEILING(SUM(KC))-SUM(YQWJ)-SUM(YZN)-SUM(EZN))*(-1)>0 ORDER BY PH,(CEILING(SUM(KC))-SUM(YQWJ)-SUM(YZN)-SUM(EZN))*(-1) DESC";
 
            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[11];
                worksheet1.Name = "二周内交货未入库产品清单(事业部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT SUBSTRING(CREATE_DATE,1,8) 日期,MF001 品号,MF003 工序号,MF007 区域,MF008 工艺说明 FROM BOMMF WHERE MF006 LIKE '6%' AND ISNULL(BOMMF.UDF02,'')='' ORDER BY SUBSTRING(CREATE_DATE,1,8) DESC";
            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "BOMMF").Tables["BOMMF"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[12];
                worksheet1.Name = "基础资料异常表(技术部)";
                int qsh = 0;
                worksheet1.Cells[1, 1] = "以下工序尚无生产线类别";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[2, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 3, i + 1] = yqjhdt.Rows[r][i].ToString();
                        qsh = r + 5;
                    }
                }

                worksheet1.Cells[qsh, 1] = "以下工单工艺生产线类别为空";
                strSql = "SELECT SUBSTRING(B.CREATE_DATE,1,8) 日期,A.TA001 工单单别,A.TA002 工单单号,A.TA003 工序号,B.TA006 图编,A.TA024 工艺说明 FROM SFCTA A LEFT JOIN MOCTA B ON A.TA001=B.TA001 AND A.TA002=B.TA002 WHERE B.TA011<>'Y' AND B.TA011<>'y' AND ISNULL(A.UDF02,'')='' AND A.TA001 LIKE '8%' ORDER BY SUBSTRING(B.CREATE_DATE,1,8) DESC";
                DataTable yqjhdt1 = db.GetDataSet1(strSql, "SFCTA").Tables["SFCTA"];

                for (int i = 0; i < yqjhdt1.Columns.Count; i++)
                {
                    worksheet1.Cells[qsh+1, i + 1] = yqjhdt1.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt1.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt1.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + qsh+2, i + 1] = yqjhdt1.Rows[r][i].ToString();
                    }
                }
            }
            catch
            {
            }

            strSql = "SELECT *,DATEDIFF(DAY,CASE WHEN 下单日期='无' THEN GETDATE() ELSE 下单日期 END,GETDATE()) 已下单天数 FROM (SELECT MQ002 订单类型,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD014 客户品号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN V_INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 WHERE TD016='N' AND TD021='Y' AND MB005='104' AND TD001='2201' AND CEILING(TD008-TD009)-CEILING(MB064)>0) A ORDER BY 品号,下单日期,预计交货日 DESC";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "COPTD").Tables["COPTD"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[13];
                worksheet1.Name = "未完成样品汇总表(技术部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT SUBSTRING(A.CREATE_DATE,1,4)+'-'+SUBSTRING(A.CREATE_DATE,5,2)+'-'+SUBSTRING(A.CREATE_DATE,7,2)+' '+SUBSTRING(A.CREATE_DATE,9,2)+':'+SUBSTRING(A.CREATE_DATE,11,2) 派工时间,UY001 派工单别,UY002 派工单号,UY003 派工日期,RTRIM(ISNULL(UY008,''))+RTRIM(ISNULL(UY006,'')) 生产线,RTRIM(ISNULL(UY013,''))+RTRIM(SUBSTRING(ISNULL(UY014,''),1,255)) 设备,UY005 品号,MB002 品名,MB003 规格,UY010 员工编号,MV002 员工姓名,CAST(A.UDF51 AS NUMERIC(16,2)) 标准工时,ISNULL(A.UDF01,'') 调机开始,ISNULL(A.UDF02,'') 调机结束,CASE WHEN ISNULL(A.UDF02,'')<>'' AND ISNULL(A.UDF01,'')<>'' THEN DATEDIFF(MINUTE,ISNULL(A.UDF01,''),ISNULL(A.UDF02,'')) ELSE 0 END 作业时间,ISNULL(A.UDF12,'待调试') 状态 FROM MOCUY A LEFT JOIN INVMB ON UY005=MB001 LEFT JOIN CMSMV ON UY010=MV001 WHERE ISNULL(A.UDF12,'')<>'调试结束' AND UY010 LIKE '8%' ORDER BY UY010,UY003";
            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCUY").Tables["MOCUY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[14];
                worksheet1.Name = "调机及模具加工本月未完成(生产部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT RTRIM(V_WLPH.TD004) as 品号,INVMB.MB002 as 品名,INVMB.MB003 as 规格,CEILING(V_WLPH.YCSL) as 预测数量,CEILING(V_WLPH.YCDD) 预测订单,CEILING(V_WLPH.DDSL) as 订单数量,CEILING(V_WLPH.KCSL) as 库存数量,CEILING(ISNULL(INVMC.MC004,0)) as 安全库存,CEILING(V_WLPH.YJSC) as 预计生产,CEILING(V_WLPH.YJQG) as 预计请购,CEILING(V_WLPH.YJJH) as 预计进货,CEILING(V_WLPH.YJLL) as 预计领料,CEILING(V_WLPH.YJJH+V_WLPH.YJQG+V_WLPH.YJSC-V_WLPH.YCSL-V_WLPH.DDSL-V_WLPH.YJLL+V_WLPH.KCSL) as 平衡状况  FROM V_WLPH  Left JOIN INVMB On V_WLPH.TD004=INVMB.MB001 LEFT JOIN INVMC ON INVMB.MB001=INVMC.MC001 AND INVMB.MB017=INVMC.MC002 WHERE EXISTS(SELECT * FROM COPTD WHERE COPTD.UDF03='暂停' AND TD016='N' AND TD004=V_WLPH.TD004)";
            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCUY").Tables["MOCUY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[15];
                worksheet1.Name = "暂停生产产品清单(生产部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }


            workbook.SaveAs(savePath1);
            // 释放资源
            workbook.Close();
            workbook = null;
            app.Quit();
            app = null;


            try
            {
                //实例化workbook对象并加载文档
                Workbook wb = new Workbook();
                wb.LoadFromFile(savePath1);

                //获取第一个工作表
                Worksheet sheet = wb.Worksheets[8];
                //获取数据范围
                CellRange range = sheet.Range["A2:O65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format3 = range.ConditionalFormats.AddCondition();
                format3.FormatType = ConditionalFormatType.Formula;
                format3.FirstFormula = "=$O2>60";
                format3.BackColor = Color.Red;

                ConditionalFormatWrapper format4 = range.ConditionalFormats.AddCondition();
                format4.FormatType = ConditionalFormatType.Formula;
                format4.SecondFormula = "=AND($O2<=60,$O2>45)";
                format4.BackColor = Color.Orange;

                ConditionalFormatWrapper format5 = range.ConditionalFormats.AddCondition();
                format5.FormatType = ConditionalFormatType.Formula;
                format5.FirstFormula = "=AND($O2<=45,$O2>=30)";
                format5.BackColor = Color.Yellow;


                //获取第二个工作表
                Worksheet sheetA = wb.Worksheets[10];
                //获取数据范围
                CellRange rangeA = sheetA.Range["A2:J65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format6 = rangeA.ConditionalFormats.AddCondition();
                format6.FormatType = ConditionalFormatType.Formula;
                format6.FirstFormula = "=$F2>60";
                format6.BackColor = Color.Red;

                ConditionalFormatWrapper format7 = rangeA.ConditionalFormats.AddCondition();
                format7.FormatType = ConditionalFormatType.Formula;
                format7.SecondFormula = "=AND($F2<=60,$F2>45)";
                format7.BackColor = Color.Orange;

                ConditionalFormatWrapper format8 = rangeA.ConditionalFormats.AddCondition();
                format8.FormatType = ConditionalFormatType.Formula;
                format8.FirstFormula = "=AND($F2<=45,$F2>=30)";
                format8.BackColor = Color.Yellow;

                //获取第三个工作表
                DateTime dta = DateTime.Now;
                DateTime dtb = dt.AddDays(-5);
                Worksheet sheetB = wb.Worksheets[7];
                //获取数据范围
                CellRange rangeB = sheetB.Range["A2:K65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format9 = rangeB.ConditionalFormats.AddCondition();
                format9.FormatType = ConditionalFormatType.Formula;
                format9.FirstFormula = "=$G2>" + dtb.ToString("yyyyMMdd") + "";
                format9.BackColor = Color.Green;

                //获取第四个工作表
                Worksheet sheetC = wb.Worksheets[12];
                //获取数据范围
                CellRange rangeC = sheetC.Range["A2:O65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format11 = rangeC.ConditionalFormats.AddCondition();
                format11.FormatType = ConditionalFormatType.Formula;
                format11.FirstFormula = "=$O2>60";
                format11.BackColor = Color.Red;

                ConditionalFormatWrapper format12 = rangeC.ConditionalFormats.AddCondition();
                format12.FormatType = ConditionalFormatType.Formula;
                format12.SecondFormula = "=AND($O2<=60,$O2>45)";
                format12.BackColor = Color.Orange;

                ConditionalFormatWrapper format13 = rangeC.ConditionalFormats.AddCondition();
                format13.FormatType = ConditionalFormatType.Formula;
                format13.FirstFormula = "=AND($O2<=45,$O2>=30)";
                format13.BackColor = Color.Yellow;

                //获取第五个工作表
                Worksheet sheetD = wb.Worksheets[3];
                //获取数据范围
                CellRange rangeD = sheet.Range["A2:N65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format14 = range.ConditionalFormats.AddCondition();
                format14.FormatType = ConditionalFormatType.Formula;
                format14.FirstFormula = "=FIND(\"逾期\",$A2,1)>0";
                format14.BackColor = Color.Red;

                ConditionalFormatWrapper format15 = range.ConditionalFormats.AddCondition();
                format15.FormatType = ConditionalFormatType.Formula;
                format15.SecondFormula = "=FIND(\"一周内\",$A2,1)>0";
                format15.BackColor = Color.Orange;

                ConditionalFormatWrapper format16 = range.ConditionalFormats.AddCondition();
                format16.FormatType = ConditionalFormatType.Formula;
                format16.FirstFormula = "=FIND(\"二周内\",$A2,1)>0";
                format16.BackColor = Color.Yellow;


                //保存并打开文档
                wb.SaveToFile(savePath, ExcelVersion.Version2007);
                File.Delete(savePath1);
                //MessageBox.Show("导出数据成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                Thread.Sleep(10000); //暂停10秒再开始发邮件
        }
            catch
            {

            }

        if(checkBox1.CheckState== System.Windows.Forms.CheckState.Checked)
            {
                SendMailUseZjqk(filename);
            }
           
           
        }

       private void button2_Click(object sender, EventArgs e)
        {
            System.Timers.Timer timer = new System.Timers.Timer();
            timer.Enabled = true;
            timer.Interval = 60000;//4秒执行间隔时间,单位为毫秒    
            timer.Start();
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Timer1_Elapsed);
        }

        private void Timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
 
            int hour = Convert.ToInt16(textBox5.Text);
            int minutes = Convert.ToInt16(textBox6.Text);
            int second = 0;

            // 得到intHour,intMinute,intSecond，是当前系统时间    
            int intHour = e.SignalTime.Hour;
            int intMinute = e.SignalTime.Minute;
            int intSecond = e.SignalTime.Second;
            // System.Windows.Forms.MessageBox.Show(intSecond.ToString());
            //自己设置hour,minuter,second,到设定的时间就开始执行程序    
            if (intHour == hour && intMinute == minutes & textBox4.Text=="运行中")
            {
                button1.PerformClick();
            }

        }

        public void SendMailUseZjqk(String filename)
        {
            DateTime dt = DateTime.Now;
            DateTime dt1 = dt.AddDays(-1);
            string zr = dt1.ToShortDateString();

            System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
            for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)
            {
                string sjr = (dataGridView1.Rows[i].Cells["EmailAdress"].Value == null) ? "" : dataGridView1.Rows[i].Cells["EmailAdress"].Value.ToString();
                if (sjr != "")
                {
                    msg.To.Add(dataGridView1.Rows[i].Cells["EmailAdress"].Value.ToString());
                }
            }
            //msg.To.Add(textBox4.Text);
            /*   
             msg.To.Add("b@b.com");   
            * msg.To.Add("b@b.com");   
            * msg.To.Add("b@b.com");可以发送给多人   
            */
            // msg.CC.Add("2670828470@qq.com");
            /*   
            * msg.CC.Add("c@c.com");   
            * msg.CC.Add("c@c.com");可以抄送给多人   
            */
            msg.From = new MailAddress(textBox1.Text, "东峰管理系统", System.Text.Encoding.UTF8);
            /* 上面3个参数分别是发件人地址（可以随便写），发件人姓名，编码*/
            msg.Subject = zr + "汽空日报表";//邮件标题    
            msg.SubjectEncoding = System.Text.Encoding.UTF8;//邮件标题编码    
            msg.Body = "各位领导：附件为" + zr + "汽空日报表";//邮件内容    
            msg.BodyEncoding = System.Text.Encoding.UTF8;//邮件内容编码    
            msg.IsBodyHtml = false;//是否是HTML邮件    
            msg.Priority = MailPriority.High;//邮件优先级    

            //以下添加附件
            string sfile = "D:\\report\\"+filename; ;//添加附件
            msg.Attachments.Add(new Attachment(sfile));

            SmtpClient client = new SmtpClient();
            client.Credentials = new System.Net.NetworkCredential(textBox1.Text, textBox3.Text);
            //见下图1
            //上述写你的QQ邮箱和密码    
            client.Port = 587;//QQ邮箱使用的端口    
            client.Host = textBox2.Text;
            client.EnableSsl = true;//经过ssl加密    
            object userState = msg;
            try
            {
                client.SendAsync(msg, userState);
                if (textBox10.Text == "")
                {
                    textBox10.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "发送成功" + filename);
                }
                else
                {
                    textBox10.AppendText("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + "发送成功" + filename);
                }
            }
            catch (System.Net.Mail.SmtpException ex)
            {
                if (textBox10.Text == "")
                {
                    textBox10.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "发送失败" + filename);
                }
                else
                {
                    textBox10.AppendText("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + "发送失败" + filename);
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (textBox3.Text == "")
            {
                MessageBox.Show("邮箱密码不能为空", "系统提示");
                return;
            }

            System.Timers.Timer timer = new System.Timers.Timer();
            timer.Enabled = true;
            timer.Interval = 60000;//4秒执行间隔时间,单位为毫秒    
            timer.Start();
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Timer1_Elapsed);
            textBox4.Text = "运行中";
            textBox4.ForeColor = Color.Green;
        }

        private void SendMails_Load(object sender, EventArgs e)
        {
            textBox4.Text = "等待中";
            textBox4.ForeColor = Color.Red;
            textBox9.Text = "等待中";
            textBox9.ForeColor = Color.Red;
            textBox15.Text = "等待中";
            textBox15.ForeColor = Color.Red;
            textBox16.Text = "等待中";
            textBox16.ForeColor = Color.Red;
            textBox21.Text = "等待中";
            textBox21.ForeColor = Color.Red;
            //加载参数
            textBox1.Text = OperatorFile.GetIniFileString("Paragram", "From", "",
                                              Application.StartupPath + "\\Email.ini");
            textBox2.Text = OperatorFile.GetIniFileString("Paragram", "Server", "",
                                             Application.StartupPath + "\\Email.ini");
            textBox5.Text = OperatorFile.GetIniFileString("Paragram", "qkhour", "",
                                                          Application.StartupPath + "\\Email.ini");
            textBox6.Text = OperatorFile.GetIniFileString("Paragram", "qkminute", "",
                                                       Application.StartupPath + "\\Email.ini");
            for(int i=1;i<=20;i++)
            {
                String yjdz= OperatorFile.GetIniFileString("Paragram", "qk"+i, "",
                                              Application.StartupPath + "\\Email.ini");
                if(yjdz !="")
                {
                    int index = dataGridView1.Rows.Add();
                    dataGridView1.Rows[index].Cells["EmailAdress"].Value = yjdz.ToString();
                }
            }

            textBox8.Text = OperatorFile.GetIniFileString("Paragram", "jkhour", "",
                                              Application.StartupPath + "\\Email.ini");
            textBox7.Text = OperatorFile.GetIniFileString("Paragram", "jkminute", "",
                                                       Application.StartupPath + "\\Email.ini");

            for (int i = 1; i <= 20; i++)
            {
                String yjdz = OperatorFile.GetIniFileString("Paragram", "jk" + i, "",
                                              Application.StartupPath + "\\Email.ini");
                if (yjdz != "")
                {
                    int index = dataGridView4.Rows.Add();
                    dataGridView4.Rows[index].Cells["EmailAdressA"].Value = yjdz.ToString();
                }
            }

            textBox12.Text = OperatorFile.GetIniFileString("Paragram", "qyhour", "",
                                  Application.StartupPath + "\\Email.ini");
            textBox13.Text = OperatorFile.GetIniFileString("Paragram", "qyminute", "",
                                                       Application.StartupPath + "\\Email.ini");

            textBox17.Text = OperatorFile.GetIniFileString("Paragram", "zjhhour", "",
                                 Application.StartupPath + "\\Email.ini");
            textBox18.Text = OperatorFile.GetIniFileString("Paragram", "zjhminute", "",
                                                       Application.StartupPath + "\\Email.ini");

            textBox19.Text = OperatorFile.GetIniFileString("Paragram", "gztshour", "",
                     Application.StartupPath + "\\Email.ini");
            textBox20.Text = OperatorFile.GetIniFileString("Paragram", "gztsminute", "",
                                                       Application.StartupPath + "\\Email.ini");

        }

        private void button4_Click(object sender, EventArgs e)
        {
            textBox4.Text = "等待中";
            textBox4.ForeColor = Color.Red;
        }

        private void button6_Click(object sender, EventArgs e)
        {
            if(textBox3.Text=="")
            {
                MessageBox.Show("邮箱密码不能为空", "系统提示");
                return;
            }
            System.Timers.Timer timer = new System.Timers.Timer();
            timer.Enabled = true;
            timer.Interval = 60000;//4秒执行间隔时间,单位为毫秒    
            timer.Start();
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Timer2_Elapsed);
            textBox9.Text = "运行中";
            textBox9.ForeColor = Color.Green;
        }

        private void Timer2_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {

            int hour = Convert.ToInt16(textBox8.Text);
            int minutes = Convert.ToInt16(textBox7.Text);
            int second = 0;

            // 得到intHour,intMinute,intSecond，是当前系统时间    
            int intHour = e.SignalTime.Hour;
            int intMinute = e.SignalTime.Minute;
            int intSecond = e.SignalTime.Second;
            // System.Windows.Forms.MessageBox.Show(intSecond.ToString());
            //自己设置hour,minuter,second,到设定的时间就开始执行程序    
            if (intHour == hour && intMinute == minutes & textBox9.Text == "运行中")
            {
                button8.PerformClick();

            }

        }

        public void SendMailUseZjjk(String filename)
        {
            DateTime dt = DateTime.Now;
            DateTime dt1 = dt.AddDays(-1);
            string zr = dt1.ToShortDateString();

            System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
            for (int i = 0; i <= dataGridView4.Rows.Count - 1; i++)
            {
                string sjr = (dataGridView4.Rows[i].Cells["EmailAdressA"].Value == null) ? "" : dataGridView4.Rows[i].Cells["EmailAdressA"].Value.ToString();
                if (sjr != "")
                {
                    msg.To.Add(dataGridView4.Rows[i].Cells["EmailAdressA"].Value.ToString());
                }
            }
            //msg.To.Add(textBox4.Text);
            /*   
             msg.To.Add("b@b.com");   
            * msg.To.Add("b@b.com");   
            * msg.To.Add("b@b.com");可以发送给多人   
            */
            // msg.CC.Add("2670828470@qq.com");
            /*   
            * msg.CC.Add("c@c.com");   
            * msg.CC.Add("c@c.com");可以抄送给多人   
            */
            msg.From = new MailAddress(textBox1.Text, "东峰管理系统", System.Text.Encoding.UTF8);
            /* 上面3个参数分别是发件人地址（可以随便写），发件人姓名，编码*/
            msg.Subject = zr + "家空日报表";//邮件标题    
            msg.SubjectEncoding = System.Text.Encoding.UTF8;//邮件标题编码    
            msg.Body = "各位领导：附件为" + zr + "家空日报表";//邮件内容    
            msg.BodyEncoding = System.Text.Encoding.UTF8;//邮件内容编码    
            msg.IsBodyHtml = false;//是否是HTML邮件    
            msg.Priority = MailPriority.High;//邮件优先级    

            //以下添加附件
            string sfile = "D:\\report\\"+filename ;//添加附件
            msg.Attachments.Add(new Attachment(sfile));

            SmtpClient client = new SmtpClient();
            client.Credentials = new System.Net.NetworkCredential(textBox1.Text, textBox3.Text);
            //见下图1
            //上述写你的QQ邮箱和密码    
            client.Port = 587;//QQ邮箱使用的端口    
            client.Host = textBox2.Text;
            client.EnableSsl = true;//经过ssl加密    
            object userState = msg;
            try
            {
                client.SendAsync(msg, userState);
                if (textBox11.Text == "")
                {
                    textBox11.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "发送成功" + filename);
                }
                else
                {
                    textBox11.AppendText("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + "发送成功" + filename);
                }
            }
            catch (System.Net.Mail.SmtpException ex)
            {
                if (textBox11.Text == "")
                {
                    textBox11.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "发送失败" + filename);
                }
                else
                {
                    textBox11.AppendText("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + "发送失败" + filename);
                }
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {

            DateTime dt = DateTime.Now;
            DateTime dt1 = dt.AddDays(-1);
            string zr = dt1.ToShortDateString();
            String filename = "家空" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            string savePath = string.Empty;
            string savePath1 = string.Empty;
            savePath = "D:\\report\\" + filename;
            savePath1 = "D:\\report\\家空temp.xlsx";
            //需要添加 Microsoft.Office.Interop.Excel引用 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            if (null == app)
            {
                MessageBox.Show("缺少Excel组件！");
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            workbook.Sheets.Add();
            string strSql = "";
            strSql = "SELECT B.QYBH 区域编号,B.QYMC 区域名称,D.MD004 标准人数,CAST(ISNULL(CQGS,0) AS NUMERIC(16,2)) 出勤工时,(SELECT SUM(CAST(ISNULL(YXGS,0) AS NUMERIC(16,2))) FROM V_GQYXGS T WHERE TE023=CONVERT(CHAR(8),GETDATE()-2,112) AND B.QYBH=T.TA006) 总有效工时,(SELECT SUM(CAST(ISNULL(S.FH*7.5,0) AS NUMERIC(16,2))) FROM V_FHMX S WHERE B.QYBH=S.GZZX) 区域总负荷,B.SCXLB 生产线类别,B.FH*7.5 总负荷,B.YYQFH*7.5 已逾期负荷,B.YZNFH*7.5 一周内负荷,B.EZNFH*7.5 二周内负荷,B.EZHFH*7.5 二周后负荷,CAST(ISNULL(YXGS,0) AS NUMERIC(16,2)) 有效工时,ISNULL(YYQ,0.00) 已逾期,ISNULL(YZN,0.00) 一周内,ISNULL(EZN,0.00) 二周内,ISNULL(EZH,0.00) 二周后";
            strSql += " FROM V_FHHZ B LEFT JOIN CMSMD D ON B.QYBH = D.MD001 LEFT JOIN CMSME E ON D.MD015 = E.ME001 LEFT JOIN (SELECT * FROM V_GQCQGS WHERE VY002 = CONVERT(CHAR(8), GETDATE() - 2, 112)) A ON A.ME001 = E.ME001";
            strSql += " LEFT JOIN (SELECT * FROM V_GQYXGS WHERE TE023 = CONVERT(CHAR(8), GETDATE() - 2, 112)) C ON C.TA006 = B.QYBH AND B.SCXLB = C.SCXLB LEFT JOIN V_YXGSMX Q ON B.QYBH = Q.TA006 AND B.SCXLB = Q.UDF02 WHERE E.ME001 LIKE '0301%' ORDER BY E.ME001,B.SCXLB";

            try
            {
                //dataGridView3.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                DataTable jkyqwbgdt = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                worksheet.Name = "各区域人工投入产出(生产)";
                for (int i = 0; i < jkyqwbgdt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = jkyqwbgdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < jkyqwbgdt.Rows.Count; r++)
                {
                    for (int i = 0; i < jkyqwbgdt.Columns.Count; i++)
                    {
                        if (i <= 3 && r > 0 && jkyqwbgdt.Rows[r][0].ToString() == jkyqwbgdt.Rows[r - 1][0].ToString())
                        {
                            worksheet.Cells[r + 2, i + 1] = "";
                        }
                        else
                        {
                            worksheet.Cells[r + 2, i + 1] = jkyqwbgdt.Rows[r][i].ToString();
                        }
                    }
                }

            }
            catch
            {
            }

            strSql = "SELECT SUBSTRING(MOCTY.CREATE_DATE,1,8) 派工日期,ISNULL(TD013,'无订单号') 成品交货日期,TY001 单别,TY002 单号,A.TA006 品号,A.TA034 品名,A.TA035 规格,CEILING(TY004) 计划数量,CEILING(TY006) 完成数量,TY003 计划完成日期,B.TA007 区域,B.TA024 工艺说明,TY009 生产线,TY020 作业员工,MV002 员工姓名 FROM MOCTY LEFT JOIN MOCTA A ON TY001=A.TA001 AND TY002=A.TA002 LEFT JOIN CMSMV ON TY020=MV001 LEFT JOIN SFCTA B ON A.TA001=B.TA001 AND A.TA002=B.TA002 AND TY019=B.TA003 LEFT JOIN COPTD ON A.TA026=TD001 AND A.TA027=TD002 AND A.TA028=TD003 WHERE TY001 LIKE '5%' AND TY003<>'' AND TY003<=CONVERT(CHAR(8),GETDATE()-2,112) AND A.TA011<>'Y' AND A.TA011<>'y' AND TY020<>'' AND TY004-TY006>0 AND TY018<>'Y'  ORDER BY B.TA006,TY003";
            try
            {
                //dataGridView3.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                DataTable jkyqwbgdt = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[2];
                worksheet.Name = "计划逾期未报工(生产)";
                for (int i = 0; i < jkyqwbgdt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = jkyqwbgdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < jkyqwbgdt.Rows.Count; r++)
                {
                    for (int i = 0; i < jkyqwbgdt.Columns.Count; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = jkyqwbgdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {
            }

            strSql = "SELECT SUBSTRING(MOCTY.CREATE_DATE,1,8) 派工日期,ISNULL(TD013,'') 成品发货日期,TY001 单别,TY002 单号,A.TA006 品号,A.TA034 品名,A.TA035 规格,B.TA024,CEILING(TY004) 计划数量,CEILING(TY006) 完成数量,CEILING(TY004-TY006) 未完成数量,TY003 计划完成日期,TY009 生产线,TY020 作业员工,MV002 员工姓名,B.TA007 区域 FROM MOCTY LEFT JOIN MOCTA A ON TY001=A.TA001 AND TY002=A.TA002 LEFT JOIN CMSMV ON TY020=MV001 LEFT JOIN SFCTA B ON A.TA001=B.TA001 AND A.TA002=B.TA002 AND TY019=B.TA003 LEFT JOIN COPTD ON A.TA026=TD001 AND A.TA027=TD002 AND A.TA028=TD003 WHERE TY001 LIKE '5%' AND TY003<>'' AND TY003=CONVERT(CHAR(8),GETDATE()-2,112) AND A.TA011<>'Y' AND A.TA011<>'y' AND TY020<>'' ORDER BY B.TA006,TY003";
            try
            {
                //dataGridView3.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                DataTable jkjhdcdt = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[3];
                worksheet1.Name = "计划达成(生产)";
                for (int i = 0; i < jkjhdcdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = jkjhdcdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < jkjhdcdt.Rows.Count; r++)
                {
                    for (int i = 0; i < jkjhdcdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = jkjhdcdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT * FROM V_QYPG WHERE 区域编号 LIKE '1%' ORDER BY 区域编号,类型";
            try
            {
                DataTable sctjdt = db.GetDataSet1(strSql, "MOCUY").Tables["MOCUY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[4];
                worksheet1.Name = "各区域需优先派工明细(生产)";
                for (int i = 0; i < sctjdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = sctjdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < sctjdt.Rows.Count; r++)
                {
                    for (int i = 0; i < sctjdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = sctjdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT SFCTA.TA007 区域,CONVERT(CHAR(8),DATEADD(DAY,0,TE023),112) 生产日期,TD003 报工日期,ISNULL(SFCTD.UDF01,'') 班次,RTRIM(ISNULL(A.UDF09,0)) 生产线,RTRIM(TE004) 员工编号,RTRIM(MV002) 员工姓名,(CASE WHEN A.UDF61=0 THEN 1 ELSE CEILING(A.UDF61) END) AS 员工个数,(CASE WHEN A.UDF62=0 THEN 1 ELSE CAST(A.UDF62 AS NUMERIC(16,6)) END) AS 数量比例,TE006 工单单别,TE007 工单单号,RTRIM(TE017) 品号,RTRIM(TE018) 品名,RTRIM(TE019) 规格,ISNULL(SUBSTRING(SFCTA.TA024,1,255),A.UDF01) 工艺说明,RTRIM(A.TE015) 备注,CEILING(CASE WHEN TE001='D420' THEN A.UDF56 ELSE CAST(TE011 AS NUMERIC(16,1)) END) 数量,CEILING(A.UDF51) 料废,CEILING(A.UDF52) 工废,CEILING(A.UDF53) 不良,CEILING((CASE WHEN SFCTA.UDF57=1 THEN 0 ELSE SFCTA.UDF57 END)*TE011) 加工工时,CAST((CASE WHEN (SFCTA.UDF57=1 OR SFCTA.UDF57=0) AND SFCTA.UDF55>0 THEN TE011/SFCTA.UDF55 ELSE 0 END) AS NUMERIC(16,2)) 生产班数,CAST(SFCTA.UDF57 AS NUMERIC(16,2)) AS 节拍,CEILING(SFCTA.UDF55) 定额,TE001 单别,TE002 单号,TE003 序号 FROM SFCTE A LEFT JOIN SFCTD ON TE001=TD001 AND TE002=TD002 LEFT JOIN SFCTA ON TE006=TA001 AND TE007=TA002 AND TE008=TA003 LEFT JOIN CMSMV ON TE004=MV001 WHERE TD003=CONVERT(CHAR(8),GETDATE()-1,112) AND TE006 LIKE '5%' AND CEILING(A.UDF51+A.UDF52+A.UDF53)>0 ORDER BY SFCTA.TA006,A.TE011,A.TE012,A.TE023,TD003,A.TE003,A.TE015,A.UDF06";
            try
            {
                DataTable sctjdt = db.GetDataSet1(strSql, "MOCUZ").Tables["MOCUZ"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[5];
                worksheet1.Name = "生产不良及报废明细(生产)";
                for (int i = 0; i < sctjdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = sctjdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < sctjdt.Rows.Count; r++)
                {
                    for (int i = 0; i < sctjdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = sctjdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT B.TA007 区域,CONVERT(CHAR(8),DATEADD(DAY,0,TE023),112) 生产日期,E.TD003 报工日期,ISNULL(E.UDF01,'') 班次,RTRIM(ISNULL(A.UDF09,0)) 生产线,ISNULL(dbo.fn_strSum11(A.TE017),'') 设备,RTRIM(TE004) 员工编号,RTRIM(MV002) 员工姓名,A.UDF58 出勤工时,ISNULL(D.TD013,'') 预计交货日,CAST((CASE WHEN DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))<0 THEN (CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011  / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54=0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END) ELSE 0 END)*7.5 AS NUMERIC(16,2)) 已逾期,CAST((CASE WHEN DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))<7 AND DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))>=0 THEN (CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011  / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54=0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END) ELSE 0 END)*7.5 AS NUMERIC(16,2)) 一周内,CAST((CASE WHEN DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))>=7 AND DATEDIFF(DAY,GETDATE(),ISNULL(D.TD013,''))<=14 THEN (CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011  / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54=0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54=0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END) ELSE 0 END)*7.5 AS NUMERIC(16,2)) 二周内,(CASE WHEN A.UDF61=0 THEN 1 ELSE CEILING(A.UDF61) END) AS 员工个数,(CASE WHEN A.UDF62=0 THEN 1 ELSE CAST(A.UDF62 AS NUMERIC(16,6)) END) AS 数量比例,TE006 工单单别,TE007 工单单号,RTRIM(TE017) 品号,RTRIM(TE018) 品名,RTRIM(TE019) 规格,ISNULL(SUBSTRING(B.TA024,1,255),A.UDF01) 工艺说明,RTRIM(A.TE015) 备注,CEILING(CASE WHEN TE001='D420' THEN A.UDF56 ELSE CAST(TE011 AS NUMERIC(16,1)) END) 数量,CEILING(A.UDF51) 料废,CEILING(A.UDF52) 工废,CEILING(A.UDF53) 不良,CEILING((CASE WHEN B.UDF57=1 THEN 0 ELSE B.UDF57 END)*TE011) 加工工时,CAST((CASE WHEN (B.UDF57=1 OR B.UDF57=0) AND B.UDF55>0 THEN TE011/B.UDF55 ELSE 0 END) AS NUMERIC(16,2)) 生产班数,CAST(B.UDF57 AS NUMERIC(16,2)) AS 节拍,CEILING(B.UDF55) 定额,CASE WHEN B.UDF57 > 1 THEN (CASE WHEN TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE CASE WHEN B.UDF54 = 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) END END) * B.UDF57 / 25200 ELSE CASE WHEN B.UDF55 = 0 THEN 0 ELSE (CASE WHEN TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) < 0 THEN 0 ELSE TE011 / (CASE WHEN B.UDF54 = 0 THEN 1 ELSE B.UDF54 END) END) / (4 * B.UDF55) END END * 7.5 有效工时,TE001 单别,TE002 单号,TE003 序号 FROM SFCTE A LEFT JOIN SFCTD E ON TE001=E.TD001 AND TE002=E.TD002 LEFT JOIN SFCTA B ON TE006=B.TA001 AND TE007=B.TA002 AND TE008=TA003 LEFT JOIN CMSMV ON TE004=MV001 LEFT JOIN MOCTA C ON TE006=C.TA001 AND TE007=C.TA002 LEFT JOIN COPTD D ON C.TA026=D.TD001 AND C.TA027=D.TD002 AND C.TA028=D.TD003 ";
            strSql += "WHERE TE023 = CONVERT(CHAR(8), GETDATE() - 2, 112) AND TE006 LIKE '5%' ORDER BY B.TA006,A.TE011,A.TE012,A.TE023,E.TD003,A.TE003,A.TE015,A.UDF06";
            try
            {
                DataTable jkscbgdt = db.GetDataSet1(strSql, "MOCTY").Tables["MOCTY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[6];
                worksheet1.Name = "报工明细(生产)";
                for (int i = 0; i < jkscbgdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = jkscbgdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < jkscbgdt.Rows.Count; r++)
                {
                    for (int i = 0; i < jkscbgdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = jkscbgdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT TC003 派工日期,ISNULL(TD013,'无订单号') 成品发货日期,TE011 工单单别,TE012 工单单号,TA006 产品图编,TA034 品名,CEILING(YCSL+DDSL) 订单数量,CEILING(YJLL) 预计领料,CEILING(MOCTC.UDF51) AS 派工数量,TE004 材料品号,MB002 材料品名,MB003 材料规格,CAST((CASE WHEN TA015=0 THEN 0 ELSE TB004/TA015 END) AS NUMERIC(16,4)) AS 单重,CAST((CASE WHEN TA015=0 THEN 0 ELSE TB004/TA015 END)*MOCTC.UDF51 AS NUMERIC(16,4)) AS 应发重量,CAST(TE005 AS NUMERIC(16,4)) 实发重量,TE006 单位,TE010 批号,TE019 是否审核 FROM MOCTE LEFT JOIN MOCTC ON TE001=TC001 AND TE002=TC002 LEFT JOIN MOCTB ON TE011=TB001 AND TE012=TB002 AND TE004=TB003 LEFT JOIN INVMB ON TE004=MB001 LEFT JOIN MOCTA ON TE011=TA001 AND TE012=TA002 LEFT JOIN V_WLPH ON TA006=TD004 LEFT JOIN COPTD ON TA026=TD001 AND TA027=TD002 AND TA028=TD003 WHERE (TC003>=CONVERT(CHAR(8),GETDATE()-5,112) OR TC009='N') AND TE011 LIKE '5%' AND (TE004 LIKE '3%' OR TE004 LIKE 'XC%') ORDER BY TC003 DESC";
            try
            {
                DataTable jkclckdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[7];
                worksheet1.Name = "材料出库明细(财务)";
                for (int i = 0; i < jkclckdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = jkclckdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < jkclckdt.Rows.Count; r++)
                {
                    for (int i = 0; i < jkclckdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = jkclckdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT ML001 品号,MB002 品名,MB003 规格,ML004 批号,MB004 单位,ML009 最近入库日,ML010 最近出库日,CAST((SELECT SUM(LA011) FROM INVLA WHERE LA001=ML001 AND LA016=ML004 AND LA005=1 AND LA009=ML002) AS NUMERIC(16,2)) AS 入库数量,ISNULL(CAST((SELECT SUM(LA011) FROM INVLA WHERE LA001=ML001 AND LA016=ML004 AND LA005=-1 AND LA009=ML002 AND (LA006 LIKE '5%' OR LA006 LIKE '12%')) AS NUMERIC(16,2)),0) AS 正常出库数量,ISNULL(CAST((SELECT SUM(LA011) FROM INVLA WHERE LA001=ML001 AND LA016=ML004 AND LA005=-1 AND LA009=ML002 AND (LA006 NOT LIKE '5%' AND LA006 NOT LIKE '12%')) AS NUMERIC(16,2)),0) AS 异常出库数量,CAST(ML005 AS NUMERIC(16,2)) 当前库存 FROM INVML LEFT JOIN INVMB ON ML001=MB001 WHERE MB005='102' AND ML009<=CONVERT(CHAR(8),GETDATE()-15,112) AND ML005>0 AND ML002<>'51100' ORDER BY ML009 DESC,ML001";
            try
            {
                DataTable clhydt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[8];
                worksheet1.Name = "15天前进货材料耗用情况(财务)";
                for (int i = 0; i < clhydt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = clhydt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < clhydt.Rows.Count; r++)
                {
                    for (int i = 0; i < clhydt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = clhydt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT *,DATEDIFF(DAY,CASE WHEN 下单日期='无' THEN GETDATE() ELSE 下单日期 END,GETDATE()) 已下单天数 FROM (SELECT MQ002 订单类型,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量,ISNULL(CJZZ,0) 车间在制 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN V_INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 LEFT JOIN V_CJZZ ON TD004=ZZPH WHERE TD016='N' AND TD021='Y' AND TD013<CONVERT(CHAR(8),GETDATE(),112) AND MB005='101' AND TD001<>'2205' AND TD001<>'2201' AND CEILING(TD008-TD009)-CEILING(MB064)>0";
            strSql += " UNION ALL SELECT '预测',(SELECT ISNULL(MIN(TC003), '无') FROM COPTD LEFT JOIN COPTC ON TD001 = TC001 AND TD002 = TC002 WHERE TD016 = 'N' AND TD004 = MF003 AND TD021 = 'Y') 下单日期,'2205',MF001 单号, MF002, MF003, MF004, MF005, MF006, CEILING(MF008)订单数, CEILING(MF009) 已交数, CEILING(MF008 - MF009) 未交数, CEILING(MB064) 库存数量, ISNULL(CJZZ, 0) 车间在制 FROM COPMF LEFT JOIN COPME ON MF001 = ME001 LEFT JOIN V_INVMB ON MF003 = MB001 LEFT JOIN V_CJZZ ON MF004 = ZZPH WHERE ME008 = 'Y' AND MF006 < CONVERT(CHAR(8), GETDATE(), 112) AND MB005 = '101' AND CEILING(MF008 - MF009) - CEILING(MB064) > 0 AND MF003 NOT LIKE 'GSX%' AND MF003 NOT LIKE 'ZG%' AND MF003 NOT LIKE 'SO%' AND MF003 NOT LIKE 'OK%') A ORDER BY 品号, 下单日期, 预计交货日 DESC";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[9];
                worksheet1.Name = "逾期未入库产品清单(事业部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT * FROM (SELECT MQ002,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 WHERE TD016='N' AND TD021='Y' AND TD013<CONVERT(CHAR(8),GETDATE(),112) AND MB005='101' AND TD001<>'2205' AND CEILING(TD008-TD009)-CEILING(MB064)<=0 ";
            strSql += "UNION ALL SELECT '预测',(SELECT ISNULL(MIN(TC003), '无') FROM COPTD LEFT JOIN COPTC ON TD001 = TC001 AND TD002 = TC002 WHERE TD016 = 'N' AND TD004 = MF003 AND TD021 = 'Y') 下单日期,'2205',MF001 单号, MF002, MF003, MF004, MF005, MF006, CEILING(MF008)订单数, CEILING(MF009) 已交数, CEILING(MF008 - MF009) 未交数, CEILING(MB064) 库存数量 FROM COPMF LEFT JOIN COPME ON MF001 = ME001 LEFT JOIN V_INVMB ON MF003 = MB001 WHERE ME008 = 'Y' AND MF006 < CONVERT(CHAR(8), GETDATE(), 112) AND MB005 = '101' AND CEILING(MF008 - MF009)-CEILING(MB064)<=0 AND MF003 NOT LIKE 'GSX%' AND MF003 NOT LIKE 'ZG%' AND MF003 NOT LIKE 'SO%' AND MF003 NOT LIKE 'OK%') A ORDER BY 品号,下单日期,预计交货日 DESC";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[10];
                worksheet1.Name = "逾期已入库未交货清单(销售)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT PH 品号,MB002,MB003,CEILING(SUM(KC)) 库存数,MIN(XDRQ) 下单日期,CASE WHEN MIN(XDRQ)='未下单' THEN 0 ELSE DATEDIFF(DAY,MIN(XDRQ),GETDATE()) END 已下单天数,SUM(YQWJ) 逾期未交货,SUM(YZN) 一周内交货,SUM(EZN) 一周到二周交货,(CEILING(SUM(KC))-SUM(YQWJ)-SUM(YZN)-SUM(EZN))*(-1) AS 欠数 FROM V_DDHZ LEFT JOIN INVMB ON PH=MB001 WHERE MB005='101' GROUP BY PH,MB002,MB003 HAVING (CEILING(SUM(KC))-SUM(YQWJ)-SUM(YZN)-SUM(EZN))*(-1)>0 ORDER BY PH,(CEILING(SUM(KC))-SUM(YQWJ)-SUM(YZN)-SUM(EZN))*(-1) DESC";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCTE").Tables["MOCTE"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[11];
                worksheet1.Name = "二周内交货未入库产品清单(事业部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT SUBSTRING(CREATE_DATE,1,8) 日期,MF001 品号,MF003 工序号,MF007 区域,MF008 工艺说明 FROM BOMMF WHERE MF006 LIKE '1%' AND ISNULL(BOMMF.UDF02,'')='' ORDER BY SUBSTRING(CREATE_DATE,1,8) DESC";
            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "BOMMF").Tables["BOMMF"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[12];
                worksheet1.Name = "基础资料异常表(技术部)";
                int qsh = 0;
                worksheet1.Cells[1, 1] = "以下工序尚无生产线类别";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[2, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 3, i + 1] = yqjhdt.Rows[r][i].ToString();
                        qsh = r + 5;
                    }
                }

                worksheet1.Cells[qsh, 1] = "以下工单工艺生产线类别为空";
                strSql = "SELECT SUBSTRING(B.CREATE_DATE,1,8) 日期,A.TA001 工单单别,A.TA002 工单单号,A.TA003 工序号,B.TA006 图编,A.TA024 工艺说明 FROM SFCTA A LEFT JOIN MOCTA B ON A.TA001=B.TA001 AND A.TA002=B.TA002 WHERE B.TA011<>'Y' AND B.TA011<>'y' AND ISNULL(A.UDF02,'')='' AND A.TA001 LIKE '5%' ORDER BY SUBSTRING(B.CREATE_DATE,1,8) DESC";
                DataTable yqjhdt1 = db.GetDataSet1(strSql, "SFCTA").Tables["SFCTA"];

                for (int i = 0; i < yqjhdt1.Columns.Count; i++)
                {
                    worksheet1.Cells[qsh + 1, i + 1] = yqjhdt1.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt1.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt1.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + qsh + 2, i + 1] = yqjhdt1.Rows[r][i].ToString();
                    }
                }
            }
            catch
            {
            }

            strSql = "SELECT *,DATEDIFF(DAY,CASE WHEN 下单日期='无' THEN GETDATE() ELSE 下单日期 END,GETDATE()) 已下单天数 FROM (SELECT MQ002 订单类型,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD014 客户品号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN V_INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 WHERE TD016='N' AND TD021='Y' AND MB005='101' AND TD001='2201' AND CEILING(TD008-TD009)-CEILING(MB064)>0) A ORDER BY 品号,下单日期,预计交货日 DESC";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "COPTD").Tables["COPTD"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[13];
                worksheet1.Name = "未完成样品汇总表(技术部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT SUBSTRING(A.CREATE_DATE,1,4)+'-'+SUBSTRING(A.CREATE_DATE,5,2)+'-'+SUBSTRING(A.CREATE_DATE,7,2)+' '+SUBSTRING(A.CREATE_DATE,9,2)+':'+SUBSTRING(A.CREATE_DATE,11,2) 派工时间,UY001 派工单别,UY002 派工单号,UY003 派工日期,RTRIM(ISNULL(UY008,''))+RTRIM(ISNULL(UY006,'')) 生产线,RTRIM(ISNULL(UY013,''))+RTRIM(SUBSTRING(ISNULL(UY014,''),1,255)) 设备,UY005 品号,MB002 品名,MB003 规格,UY010 员工编号,MV002 员工姓名,CAST(A.UDF51 AS NUMERIC(16,2)) 标准工时,ISNULL(A.UDF01,'') 调机开始,ISNULL(A.UDF02,'') 调机结束,CASE WHEN ISNULL(A.UDF02,'')<>'' AND ISNULL(A.UDF01,'')<>'' THEN DATEDIFF(MINUTE,ISNULL(A.UDF01,''),ISNULL(A.UDF02,'')) ELSE 0 END 作业时间,ISNULL(A.UDF12,'待调试') 状态 FROM MOCUY A LEFT JOIN INVMB ON UY005=MB001 LEFT JOIN CMSMV ON UY010=MV001 WHERE ISNULL(A.UDF12,'')<>'调试结束' AND UY010 LIKE '5%' ORDER BY UY010,UY003";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCUY").Tables["MOCUY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[14];
                worksheet1.Name = "调机及模具加工本月未完成(生产部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }

            strSql = "SELECT RTRIM(V_WLPH.TD004) as 品号,INVMB.MB002 as 品名,INVMB.MB003 as 规格,CEILING(V_WLPH.YCSL) as 预测数量,CEILING(V_WLPH.YCDD) 预测订单,CEILING(V_WLPH.DDSL) as 订单数量,CEILING(V_WLPH.KCSL) as 库存数量,CEILING(ISNULL(INVMC.MC004,0)) as 安全库存,CEILING(V_WLPH.YJSC) as 预计生产,CEILING(V_WLPH.YJQG) as 预计请购,CEILING(V_WLPH.YJJH) as 预计进货,CEILING(V_WLPH.YJLL) as 预计领料,CEILING(V_WLPH.YJJH+V_WLPH.YJQG+V_WLPH.YJSC-V_WLPH.YCSL-V_WLPH.DDSL-V_WLPH.YJLL+V_WLPH.KCSL) as 平衡状况  FROM V_WLPH  Left JOIN INVMB On V_WLPH.TD004=INVMB.MB001 LEFT JOIN INVMC ON INVMB.MB001=INVMC.MC001 AND INVMB.MB017=INVMC.MC002 WHERE EXISTS(SELECT * FROM COPTD WHERE COPTD.UDF03='暂停' AND TD016='N' AND TD004=V_WLPH.TD004)";

            try
            {
                DataTable yqjhdt = db.GetDataSet1(strSql, "MOCUY").Tables["MOCUY"];
                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[15];
                worksheet1.Name = "暂停生产产品清单(生产部)";
                for (int i = 0; i < yqjhdt.Columns.Count; i++)
                {
                    worksheet1.Cells[1, i + 1] = yqjhdt.Columns[i].ColumnName;
                }
                // 保存数据
                for (int r = 0; r < yqjhdt.Rows.Count; r++)
                {
                    for (int i = 0; i < yqjhdt.Columns.Count; i++)
                    {
                        worksheet1.Cells[r + 2, i + 1] = yqjhdt.Rows[r][i].ToString();
                    }
                }

            }
            catch
            {

            }


            workbook.SaveAs(savePath1);
            // 释放资源
            workbook.Close();
            workbook = null;
            app.Quit();
            app = null;


            try
            {
                //实例化workbook对象并加载文档
                Workbook wb = new Workbook();
                wb.LoadFromFile(savePath1);

                //获取第一个工作表
                Worksheet sheet = wb.Worksheets[8];
                //获取数据范围
                CellRange range = sheet.Range["A2:O65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format3 = range.ConditionalFormats.AddCondition();
                format3.FormatType = ConditionalFormatType.Formula;
                format3.FirstFormula = "=$O2>60";
                format3.BackColor = Color.Red;

                ConditionalFormatWrapper format4 = range.ConditionalFormats.AddCondition();
                format4.FormatType = ConditionalFormatType.Formula;
                format4.SecondFormula = "=AND($O2<=60,$O2>45)";
                format4.BackColor = Color.Orange;

                ConditionalFormatWrapper format5 = range.ConditionalFormats.AddCondition();
                format5.FormatType = ConditionalFormatType.Formula;
                format5.FirstFormula = "=AND($O2<=45,$O2>=30)";
                format5.BackColor = Color.Yellow;


                //获取第二个工作表
                Worksheet sheetA = wb.Worksheets[10];
                //获取数据范围
                CellRange rangeA = sheetA.Range["A2:J65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format6 = rangeA.ConditionalFormats.AddCondition();
                format6.FormatType = ConditionalFormatType.Formula;
                format6.FirstFormula = "=$F2>60";
                format6.BackColor = Color.Red;

                ConditionalFormatWrapper format7 = rangeA.ConditionalFormats.AddCondition();
                format7.FormatType = ConditionalFormatType.Formula;
                format7.SecondFormula = "=AND($F2<=60,$F2>45)";
                format7.BackColor = Color.Orange;

                ConditionalFormatWrapper format8 = rangeA.ConditionalFormats.AddCondition();
                format8.FormatType = ConditionalFormatType.Formula;
                format8.FirstFormula = "=AND($F2<=45,$F2>=30)";
                format8.BackColor = Color.Yellow;

                //获取第四个工作表
                Worksheet sheetC = wb.Worksheets[12];
                //获取数据范围
                CellRange rangeC = sheetC.Range["A2:O65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format11 = rangeC.ConditionalFormats.AddCondition();
                format11.FormatType = ConditionalFormatType.Formula;
                format11.FirstFormula = "=$O2>60";
                format11.BackColor = Color.Red;

                ConditionalFormatWrapper format12 = rangeC.ConditionalFormats.AddCondition();
                format12.FormatType = ConditionalFormatType.Formula;
                format12.SecondFormula = "=AND($O2<=60,$O2>45)";
                format12.BackColor = Color.Orange;

                ConditionalFormatWrapper format13 = rangeC.ConditionalFormats.AddCondition();
                format13.FormatType = ConditionalFormatType.Formula;
                format13.FirstFormula = "=AND($O2<=45,$O2>=30)";
                format13.BackColor = Color.Yellow;

                //获取第五个工作表
                Worksheet sheetD = wb.Worksheets[3];
                //获取数据范围
                CellRange rangeD = sheet.Range["A2:N65536"];
                // 条件格式3的类型为自定义公式
                ConditionalFormatWrapper format14 = range.ConditionalFormats.AddCondition();
                format14.FormatType = ConditionalFormatType.Formula;
                format14.FirstFormula = "=FIND(\"逾期\",$A2,1)>0";
                format14.BackColor = Color.Red;

                ConditionalFormatWrapper format15 = range.ConditionalFormats.AddCondition();
                format15.FormatType = ConditionalFormatType.Formula;
                format15.SecondFormula = "=FIND(\"一周内\",$A2,1)>0";
                format15.BackColor = Color.Orange;

                ConditionalFormatWrapper format16 = range.ConditionalFormats.AddCondition();
                format16.FormatType = ConditionalFormatType.Formula;
                format16.FirstFormula = "=FIND(\"二周内\",$A2,1)>0";
                format16.BackColor = Color.Yellow;


                //保存并打开文档
                wb.SaveToFile(savePath, ExcelVersion.Version2007);
                File.Delete(savePath1);
                //MessageBox.Show("导出数据成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                Thread.Sleep(10000); //暂停10秒再开始发邮件
            }
            catch
            {

            }
            if (checkBox1.CheckState == System.Windows.Forms.CheckState.Checked)
            {
                SendMailUseZjjk(filename);
            }
           
        }

        public void CreateExcel(string fileName)
        {
            Object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
            Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;
            Microsoft.Office.Interop.Excel.Worksheet m_objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkSheets[1];
            m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                    missing, missing, missing, missing, missing);
            m_objWorkBook.Close(false, missing, missing);
            m_objExcel.Quit();
        }

        private void ExportExcel(DataTable dt,String filename,String Sheetname)
        {
            if (null == dt || dt.Rows.Count <= 0) return;
            string savePath = string.Empty;
         
                savePath = "D:\\report\\" + filename;
          
            //需要添加 Microsoft.Office.Interop.Excel引用 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            if (null == app)
            {
                MessageBox.Show("缺少Excel组件！");
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            worksheet.Name = Sheetname; 
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                // 标题设置
                //range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                //range.Interior.ColorIndex = 15; // 背景色
                //range.Font.Bold = true; // 粗体
            }
            // 保存数据
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                }
                //rowRead++;
                //percent = ((float)(100 * rowRead)) / totalCount; // 当前进度
            }
            // 合计：
            //worksheet.Cells[DT.Rows.Count + 2, 1] = "合计";
            //worksheet.Cells[DT.Rows.Count + 2, 4] = DT.Compute("sum(Quantity)", ""); // DataTable筛选条件
            //worksheet.Cells[DT.Rows.Count + 2, 6] = DT.Compute("sum(TotalAmt)", "");
            //调整Excel的样式。
            //Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[rowCount + 2, 32]);
            //rg.Borders.LineStyle = 1; //单元格加边框
            //worksheet.Columns.AutoFit(); //自动调整列宽
            //隐藏某一行
            //选中部分单元格，把选中的单元格所在的行的Hidden属性设为true
            //worksheet.get_Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Hidden = true;
            //删除某一行
            // worksheet.get_Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
            //string savePath = "导出数据.xls";
            //string savePath = System.Windows.Forms.Application.StartupPath + "\\"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; 
            workbook.SaveAs(savePath);
            // 释放资源
            workbook.Close();
            workbook = null;
            app.Quit();
            app = null;
            MessageBox.Show("导出数据成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            textBox9.Text = "等待中";
            textBox9.ForeColor = Color.Red;
        }

        private void button12_Click(object sender, EventArgs e)
        {
              string strSql = "";
           // strSql = "SELECT MD001,MD002,RTRIM(ISNULL(UDF01,'')) FZR FROM CMSMD WHERE MD001<>'6700' AND MD001<>'6800'";
           // try
           // {
           //     DataTable dt;
           //     dataGridView3.DataSource = "";
           //     dataGridView3.Refresh();
           //     dt = db.GetDataSet1(strSql, "CMSMD").Tables["CMSMD"];
           //     dataGridView3.DataSource = dt;
           // }
           //catch
           // {
           // }
           // for(int j=0;j<=dataGridView3.Rows.Count-1;j++)
           // {
           //     string gzzx = (dataGridView3.Rows[j].Cells["MD001"].Value == null) ? "" : dataGridView3.Rows[j].Cells["MD001"].Value.ToString();
           //     string gzzxmc = (dataGridView3.Rows[j].Cells["MD002"].Value == null) ? "" : dataGridView3.Rows[j].Cells["MD002"].Value.ToString();
           //     string fzr= (dataGridView3.Rows[j].Cells["FZR"].Value == null) ? "" : dataGridView3.Rows[j].Cells["FZR"].Value.ToString();
           //     if (gzzx !="")
           //     {
                    DateTime dt = DateTime.Now;
                    DateTime dt1 = dt.AddDays(-1);
                    string zr = dt1.ToShortDateString();
                    String filename = "管理报表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    string savePath = string.Empty;
                    string savePath1 = string.Empty;
                    savePath = "D:\\report\\" + filename;
                    savePath1 = "D:\\report\\" + "管理报表" + "temp.xlsx";
                    //需要添加 Microsoft.Office.Interop.Excel引用 
                    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                    if (null == app)
                    {
                        MessageBox.Show("缺少Excel组件！");
                        return;
                    }
                    app.Visible = false;
                    app.UserControl = true;
                    Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                    workbook.Sheets.Add();

                    strSql = " SELECT MQ002 订单类型,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 WHERE TD016='N' AND TD021='Y' AND TD013<CONVERT(CHAR(8),GETDATE(),112) AND CEILING(TD008-TD009)-CEILING(MB064)<=0 ORDER BY 品号,预计交货日 DESC";
                    try
                    {
                        //dataGridView2.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                        DataTable yqwbgdt = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                        worksheet.Name = "已入库已逾期未销货清单";
                        for (int i = 0; i < yqwbgdt.Columns.Count; i++)
                        {
                            worksheet.Cells[1, i + 1] = yqwbgdt.Columns[i].ColumnName;
                        }
                        // 保存数据
                        for (int r = 0; r < yqwbgdt.Rows.Count; r++)
                        {
                            for (int i = 0; i < yqwbgdt.Columns.Count; i++)
                            {
                                worksheet.Cells[r + 2, i + 1] = yqwbgdt.Rows[r][i].ToString();
                            }
                        }
                    }
                    catch
                    {
                    }

                    strSql = "SELECT MQ002 订单类型,TC003 下单日期,TD001 单别,TD002 单号,TD003 序号,TD004 品号,TD005 品名,TD006 规格,TD013 预计交货日,CEILING(TD008) 订单数,CEILING(TD009) 已交数,CEILING(TD008-TD009) 未交数,CEILING(MB064) 库存数量 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 LEFT JOIN INVMB ON TD004=MB001 LEFT JOIN CMSMQ ON TD001=MQ001 WHERE TD016='N' AND TD021='Y' AND TD013<CONVERT(CHAR(8),GETDATE(),112) AND CEILING(TD008-TD009)-CEILING(MB064)>0 ORDER BY 品号,预计交货日 DESC";
                    try
                    {
                        //dataGridView2.DataSource = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                        DataTable yqwbgdt = db.GetDataSet1(strSql, "QYPG").Tables["QYPG"];
                        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[2];
                        worksheet.Name = "未入库已逾期销货清单";
                        for (int i = 0; i < yqwbgdt.Columns.Count; i++)
                        {
                            worksheet.Cells[1, i + 1] = yqwbgdt.Columns[i].ColumnName;
                        }
                        // 保存数据
                        for (int r = 0; r < yqwbgdt.Rows.Count; r++)
                        {
                            for (int i = 0; i < yqwbgdt.Columns.Count; i++)
                            {
                                worksheet.Cells[r + 2, i + 1] = yqwbgdt.Rows[r][i].ToString();
                            }
                        }
                    }
                    catch
                    {
                    }
                    workbook.SaveAs(savePath1);
                    // 释放资源
                    workbook.Close();
                    workbook = null;
                    app.Quit();
                    app = null;
                    try
                    {
                        //实例化workbook对象并加载文档
                        Workbook wb = new Workbook();
                        wb.LoadFromFile(savePath1);

                        ////获取第一个工作表
                        //Worksheet sheet = wb.Worksheets[1];
                        ////获取数据范围
                        //CellRange range = sheet.Range["A2:N65536"];
                        //// 条件格式3的类型为自定义公式
                        //ConditionalFormatWrapper format3 = range.ConditionalFormats.AddCondition();
                        //format3.FormatType = ConditionalFormatType.Formula;
                        //format3.FirstFormula = "=FIND(\"逾期\",$A2,1)>0";
                        //format3.BackColor = Color.Red;

                        //ConditionalFormatWrapper format4 = range.ConditionalFormats.AddCondition();
                        //format4.FormatType = ConditionalFormatType.Formula;
                        //format4.SecondFormula = "=FIND(\"一周内\",$A2,1)>0";
                        //format4.BackColor = Color.Orange;

                        //ConditionalFormatWrapper format5 = range.ConditionalFormats.AddCondition();
                        //format5.FormatType = ConditionalFormatType.Formula;
                        //format5.FirstFormula = "=FIND(\"二周内\",$A2,1)>0";
                        //format5.BackColor = Color.Yellow;
                  
                        //保存并打开文档
                        wb.SaveToFile(savePath, ExcelVersion.Version2007);
                        File.Delete(savePath1);
                    }
                    catch
                    {
                    }
                    //MessageBox.Show("导出数据成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    Thread.Sleep(2000); //暂停10秒再开始发邮件
                    String fzr = "LaiXuanbin|DanShuiHuangGaoTai|13858901002|13666827511";
                    SendWeixin(filename,fzr);
            //    }
            //}
         }

        public void SendWeixin(String filename,String sjr)
        {
            var clientA = new RestClient("https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=ww9f0e8d7105c6b0e7&corpsecret=t0lUOU_MRI3MkZIt5VgVVLbc_qbV--CEzGXgu9pmDSU");
            clientA.Timeout = -1;
            var requestA = new RestRequest(Method.GET);
            IRestResponse responseString = clientA.Execute(requestA);
            Logininfor lg = JsonConvert.DeserializeObject<Logininfor>(responseString.Content.ToString());
            string tokenstring = lg.access_token.ToString();         
            string attachment_id = "";
            string sfile = "D:\\report\\" + filename;

            var client = new RestClient("https://qyapi.weixin.qq.com/cgi-bin/media/upload?access_token=" + tokenstring + "&type=file");
            client.Timeout = -1;
            var request = new RestRequest(Method.POST);
            request.AddHeader("Content-Type", "application/octet-stream");
            request.AddParameter("name", "media");
            request.AddFile("filename", sfile);
            request.AddParameter("filelength", "6");
            IRestResponse response = client.Execute(request);
            AttachmentA attach = JsonConvert.DeserializeObject<AttachmentA>(response.Content.ToString());
            attachment_id = attach.media_id.ToString();


            //以上登录并获取token
            //接下去推送微信消息

            client = new RestClient("https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=" + tokenstring + "");
            request = new RestRequest(Method.POST);
            client.Timeout = -1;
            request.AddHeader("Content-Type", "application/json");
            request.AddParameter("application/json", "{\r\n   \"touser\" : \"" + sjr +"\",\r\n   \"msgtype\" : \"file\",\r\n   \"agentid\" : 1000002,\r\n   \"file\" : {\r\n        \"media_id\" : \"" + attachment_id + "\"\r\n   },\r\n   \"safe\":0,\r\n   \"enable_duplicate_check\": 0,\r\n   \"duplicate_check_interval\": 1800\r\n}", ParameterType.RequestBody);
            response = client.Execute(request);           
        }

        private void button10_Click(object sender, EventArgs e)
        {
            System.Timers.Timer timer = new System.Timers.Timer();
            timer.Enabled = true;
            timer.Interval = 60000;//4秒执行间隔时间,单位为毫秒    
            timer.Start();
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Timer3_Elapsed);
            textBox15.Text = "运行中";
            textBox15.ForeColor = Color.Green;
        }

        private void Timer3_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {

            int hour = Convert.ToInt16(textBox12.Text);
            int minutes = Convert.ToInt16(textBox13.Text);
            // 得到intHour,intMinute,intSecond，是当前系统时间    
            int intHour = e.SignalTime.Hour;
            int intMinute = e.SignalTime.Minute;
            int intSecond = e.SignalTime.Second;
            // System.Windows.Forms.MessageBox.Show(intSecond.ToString());
            //自己设置hour,minuter,second,到设定的时间就开始执行程序    
            if (intHour == hour && intMinute == minutes & textBox15.Text == "运行中")
            {
                button12.PerformClick();
            }

        }

        private void button9_Click(object sender, EventArgs e)
        {
            textBox15.Text = "等待中";
            textBox15.ForeColor = Color.Red;
        }

        private void button13_Click(object sender, EventArgs e)
        {
            String strSql = "";

            strSql = "SELECT TC004 客户编号,KHDH 客户单号,KHHH 客户行号,TD001 单别,TD002 单号,TD003 序号,RTRIM(TD004) 品号,TD007 仓库,CEILING(TD008) 订单数量 ,CEILING(TD009) 已交数量,CEILING(TD008-TD009) AS 未交数量,(CASE WHEN YJJH='' THEN CONVERT(CHAR(8),GETDATE(),112) ELSE YJJH END) AS 预计交货日,0.00 AS 已核数量,MB017 主要仓库 FROM MRPTD LEFT JOIN INVMB ON TD004=MB001 LEFT JOIN COPMA ON TC004=MA001 WHERE (TD004 LIKE '" + textBox1.Text + "%' OR '" + textBox1.Text + "'='') AND (TC004 LIKE '" + textBox2.Text + "%' OR '" + textBox2.Text + "'='') AND (MA002 LIKE '" + textBox3.Text + "%' OR '" + textBox3.Text + "'='') AND Level=0";
            //MessageBox.Show(strSql,"提示");
            try
            {
                DataTable dt;
                dataGridView5.DataSource = "";
                dataGridView5.Refresh();

                dt = db.GetDataSet1(strSql, "COPTD").Tables["COPTD"];
                dataGridView5.DataSource = dt;
                this.dataGridView5.Columns["已核数量"].Visible = false;
                this.dataGridView5.Columns["主要仓库"].Visible = false;
                this.dataGridView5.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView5.AllowUserToAddRows = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }

            //先清除原有需求及供给档
            strSql = "DELETE FROM MRPTD";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            strSql = "DELETE FROM MRPTE";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            strSql = "DELETE FROM MRPTF";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            strSql = "DELETE FROM MRPTG";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            strSql = "DELETE FROM MRPBA";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            //插入需求数据(订单数据)
            strSql = "INSERT INTO MRPTD SELECT TC004 客户编号,COPTD.UDF01 客户单号,COPTD.UDF02 客户行号,TD001 单别,TD002 单号,TD003 序号,TD004 品号,TD007 仓库,CEILING(TD008) 订单数量 ,CEILING(TD009) 已交数量,TD008-TD009 AS 未交数量,(CASE WHEN TD013='' THEN TC003 ELSE TD013 END) AS 预计交货日,0 FROM COPTD LEFT JOIN COPTC ON TD001=TC001 AND TD002=TC002 WHERE TD016 = 'N' AND ISNULL(COPTC.UDF01, '')<> 'Y' AND TD021 = 'Y' AND NOT EXISTS(SELECT * FROM MRPTD WHERE COPTD.TD001 = MRPTD.TD001 AND COPTD.TD002 = MRPTD.TD002 AND COPTD.TD003 = MRPTD.TD003) AND NOT EXISTS(SELECT * FROM COPMF LEFT JOIN COPME ON MF001=ME001 WHERE ME008='Y' AND MF003=TD004)";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            //插入需求数据(销售预测数据)
            strSql = "INSERT INTO MRPTD SELECT ME002,ISNULL(COPMF.UDF01,'') 客户单号,ISNULL(COPMF.UDF02,'') 客户行号,'预测' 单别,MF001 单号,MF002 序号,MF003 品号,MF007,CEILING(MF008) 订单数量 ,CEILING(MF009) 已交数量,MF008-MF009 AS 未交数量,(CASE WHEN MF006='' THEN CONVERT(CHAR(8),GETDATE(),112) ELSE MF006 END) AS 预计交货日,0 FROM COPMF LEFT JOIN COPME ON MF001=ME001 WHERE (CASE WHEN MF006='' THEN CONVERT(CHAR(8),GETDATE(),112) ELSE MF006 END)<=CONVERT(CHAR(8),GETDATE()+60,112) AND ME008 = 'Y' AND NOT EXISTS(SELECT * FROM MRPTD WHERE MRPTD.TD001='预测' AND COPMF.MF001 = MRPTD.TD002 AND COPMF.MF002 = MRPTD.TD003)";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            //插入库存数据
            strSql = "INSERT INTO MRPTE  SELECT MC001,MC002,MC007,0 YHXL FROM INVMC WHERE MC007> 0 ORDER BY MC002 DESC";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            //插入在制数据
            strSql = "INSERT INTO MRPTF SELECT MOCTA.TA006,MOCTA.TA001,MOCTA.TA002,SFCTA.TA003,(CASE WHEN SFCTA.TA010-SFCTA.TA011-SFCTA.TA012>MOCTA.TA015-MOCTA.TA017 THEN CASE WHEN (MOCTA.TA015-MOCTA.TA017)<0 THEN 0 ELSE MOCTA.TA015-MOCTA.TA017 END ELSE CASE WHEN SFCTA.TA010-SFCTA.TA011-SFCTA.TA012<0 THEN 0 ELSE SFCTA.TA010-SFCTA.TA011-SFCTA.TA012 END END) ZZSL,0 as YHXL FROM SFCTA LEFT JOIN MOCTA ON SFCTA.TA001 = MOCTA.TA001 AND SFCTA.TA002 = MOCTA.TA002 WHERE MOCTA.TA011 <> 'Y' AND MOCTA.TA011 <> 'y' AND MOCTA.TA013='Y' AND SFCTA.TA010 - SFCTA.TA011-SFCTA.TA012> 0 AND NOT EXISTS(SELECT * FROM SFCTA C WHERE SFCTA.TA001=C.TA001 AND SFCTA.TA002=C.TA002 AND C.TA010-C.TA011<0) ORDER BY MOCTA.TA009";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            //插入未开工工单数据
            strSql = "INSERT INTO MRPTG  SELECT A.TA006,A.TA001,A.TA002,TA015-ISNULL(B.ZZSL,0),0 AS YHXL FROM MOCTA A LEFT JOIN V_MRPTF B ON A.TA001=B.TA001 AND A.TA002=B.TA002 WHERE A.TA011<>'Y' AND A.TA011<>'y' AND A.TA013='Y' AND TA015-ISNULL(B.ZZSL,0)>0";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            for (int a = 0; a <= 20; a++)  //逐阶循环,最多20阶
            {
                //先查出本阶次的需求数据
                strSql = "SELECT TC004 客户编号,A.TD001 单别,A.TD002 单号,A.TD003 序号,A.TD004 品号,A.TD007 仓库编号,CEILING(A.TD008) 订单数量 ,CEILING(A.TD009) 已交数量,A.WJSL AS 未交数量,YJJH AS 预计交货日,0 AS 已核数量,MB017 主要仓库 FROM MRPTD A LEFT JOIN COPTD B ON A.TD001=B.TD001 AND A.TD002=B.TD002 AND A.TD003=B.TD003 LEFT JOIN INVMB ON A.TD004=MB001 WHERE A.Level=" + a + " ORDER BY A.YJJH";
                //MessageBox.Show(strSql,"提示"+a);
                try
                {

                    DataTable dt;
                    dataGridView5.DataSource = "";
                    dataGridView5.Refresh();
                    dt = db.GetDataSet1(strSql, "COPTD").Tables["COPTD"];
                    dataGridView5.DataSource = dt;
                    if (dataGridView5.Rows.Count > 1)
                    {
                        this.dataGridView5.Columns["已核数量"].Visible = false;
                        this.dataGridView5.Columns["主要仓库"].Visible = false;
                        this.dataGridView5.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                        this.dataGridView5.AllowUserToAddRows = false;
                    }
                    else
                    {
                        button16.PerformClick();
                        return;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "软件提示");
                    //throw ex;
                }


                progressBar1.Visible = true;
                progressBar1.Maximum = dataGridView5.Rows.Count;//设置最大长度值
                progressBar1.Value = 0;//设置当前值
                progressBar1.Step = 1;//设置没次增长多少

                //接下去进行循环运算每笔需求的供给状况，并插入到运算档里
                for (int i = 0; i < dataGridView5.Rows.Count - 1; i++)
                {
                    progressBar1.Value += progressBar1.Step;
                    //先取出当行各列的值并标准化，以免出现null值
                    string khbh = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["客户编号"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["客户编号"].Index].Value.ToString();
                    string dddb = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["单别"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["单别"].Index].Value.ToString();
                    string dddh = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["单号"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["单号"].Index].Value.ToString();
                    string ddxh = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["序号"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["序号"].Index].Value.ToString();
                    string ddph = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["品号"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["品号"].Index].Value.ToString();
                    string ddck = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["仓库编号"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["仓库编号"].Index].Value.ToString();
                    double ddsl = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["订单数量"].Index].Value == null) ? 0 : Convert.ToDouble(this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["订单数量"].Index].Value.ToString());
                    double wjsl = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["未交数量"].Index].Value == null) ? 0 : Convert.ToDouble(this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["未交数量"].Index].Value.ToString());
                    double yjsl = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已交数量"].Index].Value == null) ? 0 : Convert.ToDouble(this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已交数量"].Index].Value.ToString());
                    string yjjh = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["预计交货日"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["预计交货日"].Index].Value.ToString();
                    string zyck = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["主要仓库"].Index].Value == null) ? "" : this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["主要仓库"].Index].Value.ToString();
                    double yhsl = (this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value == null) ? 0 : Convert.ToDouble(this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value.ToString());
                    double kcsl = 0;
                    double whsl = wjsl - yhsl;
                    //1.先检查订单里指定的仓库库存（如广州仓、珠海仓、成品仓等），并进行核销
                    strSql = "SELECT (MC007-YHXL) AS MC007 FROM MRPTE  WHERE MC001 ='" + ddph + "' AND MC002='" + ddck + "'";
                    try
                    {
                        sdr1 = db.GetDataReader1(strSql);
                        while (sdr1.Read())
                        {
                            kcsl = Convert.ToDouble(sdr1["MC007"].ToString());
                        }
                        if (sdr1.HasRows == false)
                        {
                            kcsl = 0;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "软件提示");
                        throw ex;
                    }
                    finally
                    {
                        sdr1.Close();
                    }

                    if (whsl > 0 && kcsl > 0)    //未核销数大于0才执行
                    {
                        if (whsl >= kcsl)   //如果未核销数量大于等于库存数量
                        {
                            strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','库存','','','','" + ddck + "'," + kcsl + ",'','" + ddph + "','" + yjjh + "'";
                            try
                            {
                                if (db.ExecDataBySql1(strSql) > 0)
                                {
                                    //更新界面已核数量

                                    this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();

                                    //更新供给方已核数量
                                    strSql = "UPDATE MRPTE SET YHXL=YHXL+" + kcsl + " WHERE MC001 ='" + ddph + "' AND MC002='" + ddck + "'";
                                    try
                                    {
                                        if (db.ExecDataBySql1(strSql) > 0)
                                        {
                                            whsl = whsl - kcsl;
                                        }
                                        else
                                        {
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        MessageBox.Show(ex.Message, "软件提示");
                                        throw ex;
                                    }

                                }
                                else
                                {
                                }
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.Message, "软件提示");
                                throw ex;
                            }

                        }
                        else     //如果未核销数量小于库存数量
                        {
                            strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','库存','','','','" + ddck + "'," + whsl + ",'','" + ddph + "','" + yjjh + "'";

                            try
                            {
                                if (db.ExecDataBySql1(strSql) > 0)
                                {
                                    //更新界面已核数量
                                    yhsl = yhsl + whsl;
                                    this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();
                                    //更新供给方已核数量
                                    strSql = "UPDATE MRPTE SET YHXL=YHXL+" + whsl + " WHERE MC001='" + ddph + "' AND MC002='" + ddck + "'";
                                    try
                                    {
                                        if (db.ExecDataBySql1(strSql) > 0)
                                        {
                                            whsl = 0;
                                        }
                                        else
                                        {
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        MessageBox.Show(ex.Message, "软件提示");
                                        throw ex;
                                    }
                                }
                                else
                                {
                                }
                            }
                            catch
                            {
                            }
                        }
                    }
                    //2.检查主要仓库库存（如汽空成品仓核家空成品仓等），并进行核销

                    if (whsl > 0)    //如果未核数量大于0,才继续核销
                    {
                        strSql = "SELECT (MC007-YHXL) AS MC007 FROM MRPTE  WHERE MC001 ='" + ddph + "' AND MC002='" + zyck + "'";
                        try
                        {
                            sdr1 = db.GetDataReader1(strSql);
                            while (sdr1.Read())
                            {
                                kcsl = Convert.ToDouble(sdr1["MC007"].ToString());
                            }
                            if (sdr1.HasRows == false)
                            {
                                kcsl = 0;
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "软件提示");
                            throw ex;
                        }
                        finally
                        {
                            sdr1.Close();
                        }

                        if (kcsl > 0)  //如果库存数量大于0才执行
                        {
                            //如果未核销数量大于等于库存数量
                            if (whsl >= kcsl)
                            {
                                strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','库存','','','','" + zyck + "'," + kcsl + ",'','" + ddph + "','" + yjjh + "'";

                                try
                                {
                                    if (db.ExecDataBySql1(strSql) > 0)
                                    {
                                        //更新界面已核数量
                                        yhsl = yhsl + kcsl;
                                        this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();

                                        //更新供给方已核数量                                    
                                        strSql = "UPDATE MRPTE SET YHXL=YHXL+" + kcsl + " WHERE MC001 ='" + ddph + "' AND MC002='" + zyck + "'";
                                        try
                                        {
                                            if (db.ExecDataBySql1(strSql) > 0)
                                            {
                                                whsl = whsl - kcsl;
                                            }
                                            else
                                            {
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            MessageBox.Show(ex.Message, "软件提示");
                                            throw ex;
                                        }

                                    }
                                    else
                                    {
                                    }
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show(ex.Message, "软件提示");
                                    throw ex;
                                }

                            }
                            else     //如果未核销数量小于库存数量
                            {
                                strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','库存','','','','" + zyck + "'," + whsl + ",'','" + ddph + "','" + yjjh + "'";
                                try
                                {
                                    if (db.ExecDataBySql1(strSql) > 0)
                                    {
                                        //更新界面已核数量
                                        yhsl = yhsl + whsl;
                                        this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();
                                        //更新供给方已核数量
                                        strSql = "UPDATE MRPTE SET YHXL=YHXL+" + whsl + " WHERE MC001='" + ddph + "' AND MC002='" + zyck + "'";

                                        try
                                        {
                                            if (db.ExecDataBySql1(strSql) > 0)
                                            {
                                                whsl = 0;
                                            }
                                            else
                                            {
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            MessageBox.Show(ex.Message, "软件提示");
                                            throw ex;
                                        }
                                    }
                                    else
                                    {
                                    }
                                }
                                catch
                                {
                                }
                            }
                        }
                    }

                    //3.检查车间在制
                    if (whsl > 0)    //如果未核数量大于0,才继续核销车间在制
                    {
                        int ZZBS = 0;
                        strSql = "SELECT COUNT(*) AS ZZBS FROM MRPTF  WHERE TA006 ='" + ddph + "'";
                        try
                        {
                            sdr1 = db.GetDataReader1(strSql);
                            while (sdr1.Read())
                            {
                                ZZBS = Convert.ToInt32(sdr1["ZZBS"].ToString());
                            }
                            if (sdr1.HasRows == false)
                            {
                                ZZBS = 0;
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "软件提示");
                            throw ex;
                        }
                        finally
                        {
                            sdr1.Close();
                        }

                        for (int j = 1; j <= ZZBS; j++)
                        {
                            if (whsl > 0)    //如果未核数量大于0,才继续核销下一笔车间在制
                            {
                                string gdid = "";
                                string gddb = "";
                                string gddh = "";
                                string gdxh = "";
                                double zzsl = 0;

                                strSql = "SELECT ID,TA001,TA002,TA003,(ZZSL-YHXL) AS WHXL FROM MRPTF A WHERE TA006 ='" + ddph + "' AND ZZSL-YHXL>0 AND NOT EXISTS(SELECT * FROM MRPTF B WHERE A.TA006=B.TA006 AND B.ZZSL-B.YHXL>0 AND A.ID>B.ID)";
                                try
                                {
                                    sdr1 = db.GetDataReader1(strSql);
                                    while (sdr1.Read())
                                    {
                                        gdid = sdr1["ID"].ToString();
                                        gddb = sdr1["TA001"].ToString();
                                        gddh = sdr1["TA002"].ToString();
                                        gdxh = sdr1["TA003"].ToString();
                                        zzsl = Convert.ToDouble(sdr1["WHXL"].ToString());
                                    }
                                    if (sdr1.HasRows == false)
                                    {
                                        zzsl = 0;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show(ex.Message, "软件提示");
                                    throw ex;
                                }
                                finally
                                {
                                    sdr1.Close();
                                }

                                if (zzsl > 0)  //在制数量大于0才执行
                                {
                                    //如果未核销数量大于等于在制数量
                                    if (whsl >= zzsl)
                                    {
                                        strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','在制','" + gddb + "','" + gddh + "','" + gdxh + "',''," + zzsl + ",'','" + ddph + "','" + yjjh + "'";
                                        try
                                        {
                                            if (db.ExecDataBySql1(strSql) > 0)
                                            {
                                                //更新界面已核数量
                                                yhsl = yhsl + zzsl;
                                                this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();

                                                //更新供给方已核数量
                                                strSql = "UPDATE MRPTF SET YHXL=YHXL+" + zzsl + " WHERE ID ='" + gdid + "'";
                                                try
                                                {
                                                    if (db.ExecDataBySql1(strSql) > 0)
                                                    {
                                                        whsl = whsl - zzsl;
                                                    }
                                                    else
                                                    {
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    MessageBox.Show(ex.Message, "软件提示");
                                                    throw ex;
                                                }

                                            }
                                            else
                                            {
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            MessageBox.Show(ex.Message, "软件提示");
                                            throw ex;
                                        }

                                    }
                                    else     //如果未核销数量小于在制数量
                                    {
                                        strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','在制','" + gddb + "','" + gddh + "','" + gdxh + "',''," + whsl + ",'','" + ddph + "','" + yjjh + "'";
                                        try
                                        {
                                            if (db.ExecDataBySql1(strSql) > 0)
                                            {
                                                //更新界面已核数量

                                                this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();
                                                //更新供给方已核数量
                                                strSql = "UPDATE MRPTF SET YHXL=YHXL+" + whsl + " WHERE ID='" + gdid + "'";
                                                try
                                                {
                                                    if (db.ExecDataBySql1(strSql) > 0)
                                                    {
                                                        whsl = 0;
                                                    }
                                                    else
                                                    {
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    MessageBox.Show(ex.Message, "软件提示");
                                                    throw ex;
                                                }
                                            }
                                            else
                                            {
                                            }
                                        }
                                        catch
                                        {
                                        }
                                    }
                                }
                            }
                        }
                    }

                    //4.检查未开工工单
                    if (whsl > 0)    //如果未核数量大于0,才继续核销未开工工单
                    {
                        int ZZBS = 0;
                        strSql = "SELECT COUNT(*) AS ZZBS FROM MRPTG  WHERE TA006 ='" + ddph + "'";
                        try
                        {
                            sdr1 = db.GetDataReader1(strSql);
                            while (sdr1.Read())
                            {
                                ZZBS = Convert.ToInt32(sdr1["ZZBS"].ToString());
                            }
                            if (sdr1.HasRows == false)
                            {
                                ZZBS = 0;
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "软件提示");
                            throw ex;
                        }
                        finally
                        {
                            sdr1.Close();
                        }

                        for (int k = 1; k <= ZZBS; k++)
                        {
                            if (whsl > 0)    //如果未核数量大于0,才继续核销下一笔工单
                            {
                                string gdid = "";
                                string gddb = "";
                                string gddh = "";
                                double zzsl = 0;

                                strSql = "SELECT ID,TA001,TA002,(TA015-YHXL) AS WHXL FROM MRPTG A WHERE TA006 ='" + ddph + "' AND TA015-YHXL>0 AND NOT EXISTS(SELECT * FROM MRPTG B WHERE A.TA006=B.TA006 AND B.TA015-B.YHXL>0 AND A.ID>B.ID)";
                                try
                                {
                                    sdr1 = db.GetDataReader1(strSql);
                                    while (sdr1.Read())
                                    {
                                        gdid = sdr1["ID"].ToString();
                                        gddb = sdr1["TA001"].ToString();
                                        gddh = sdr1["TA002"].ToString();
                                        zzsl = Convert.ToDouble(sdr1["WHXL"].ToString());
                                    }
                                    if (sdr1.HasRows == false)
                                    {
                                        zzsl = 0;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show(ex.Message, "软件提示");
                                    throw ex;
                                }
                                finally
                                {
                                    sdr1.Close();
                                }

                                if (zzsl > 0)  //工单数量大于0才执行
                                {
                                    //如果未核销数量大于等于工单数量
                                    if (whsl >= zzsl)
                                    {
                                        strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','工单','" + gddb + "','" + gddh + "','',''," + zzsl + ",'','" + ddph + "','" + yjjh + "'";
                                        try
                                        {
                                            if (db.ExecDataBySql1(strSql) > 0)
                                            {
                                                //更新界面已核数量
                                                yhsl = yhsl + zzsl;
                                                this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();

                                                //插入下阶需求数据
                                                strSql = "INSERT INTO MRPTD SELECT '" + khbh + "','" + gddb + "','" + gddh + "','" + dddb + "','" + dddh + "','" + ddxh + "',TB003 下阶品号,MB017,CEILING(" + zzsl + "*TB004/TA015) 订单数量 ,0 AS 已交数量,CEILING(" + zzsl + "*TB004/TA015)  AS 未交数量,'" + yjjh + "' AS 预计交货日," + (a + 1) + " FROM MOCTB LEFT JOIN MOCTA ON TB001=TA001 AND TB002=TA002 LEFT JOIN INVMB ON TB003=MB001 WHERE MB025='M' AND TB001='" + gddb + "' AND TB002='" + gddh + "'";
                                                try
                                                {
                                                    if (db.ExecDataBySql1(strSql) > 0)
                                                    {

                                                    }
                                                    else
                                                    {
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    MessageBox.Show(ex.Message, "软件提示");
                                                    throw ex;
                                                }

                                                //更新供给方已核数量
                                                strSql = "UPDATE MRPTG SET YHXL=YHXL+" + zzsl + " WHERE ID ='" + gdid + "'";
                                                try
                                                {
                                                    if (db.ExecDataBySql1(strSql) > 0)
                                                    {
                                                        whsl = whsl - zzsl;
                                                    }
                                                    else
                                                    {
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    MessageBox.Show(ex.Message, "软件提示");
                                                    throw ex;
                                                }

                                            }
                                            else
                                            {
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            MessageBox.Show(ex.Message, "软件提示");
                                            throw ex;
                                        }

                                    }
                                    else     //如果未核销数量小于在制数量
                                    {
                                        strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','工单','" + gddb + "','" + gddh + "','',''," + whsl + ",'','" + ddph + "','" + yjjh + "'";
                                        try
                                        {
                                            if (db.ExecDataBySql1(strSql) > 0)
                                            {
                                                //更新界面已核数量

                                                this.dataGridView5.Rows[i].Cells[this.dataGridView5.Columns["已核数量"].Index].Value = yhsl.ToString();

                                                //插入下阶需求数据
                                                strSql = "INSERT INTO MRPTD SELECT '" + khbh + "','" + gddb + "','" + gddh + "','" + dddb + "','" + dddh + "','" + ddxh + "',MD003 下阶品号,MB017,CEILING(" + whsl + "*MD006/MD007) 订单数量 ,0 AS 已交数量,CEILING(" + whsl + "*MD006/MD007)  AS 未交数量,'" + yjjh + "' AS 预计交货日," + (a + 1) + " FROM BOMMD LEFT JOIN INVMB ON MD003=MB001 WHERE MB025='M' AND MD001='" + ddph + "'";
                                                try
                                                {
                                                    if (db.ExecDataBySql1(strSql) > 0)
                                                    {

                                                    }
                                                    else
                                                    {
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    MessageBox.Show(ex.Message, "软件提示");
                                                    throw ex;
                                                }

                                                //更新供给方已核数量
                                                strSql = "UPDATE MRPTG SET YHXL=YHXL+" + whsl + " WHERE ID='" + gdid + "'";
                                                try
                                                {
                                                    if (db.ExecDataBySql1(strSql) > 0)
                                                    {
                                                        whsl = 0;
                                                    }
                                                    else
                                                    {
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    MessageBox.Show(ex.Message, "软件提示");
                                                    throw ex;
                                                }

                                            }
                                            else
                                            {
                                            }
                                        }
                                        catch
                                        {
                                        }

                                    }
                                }
                            }
                        }
                    }

                    //5.核销完还不够的部分,即缺工单部分直接插入核销档
                    if (whsl > 0)
                    {
                        strSql = "INSERT INTO MRPBA  SELECT '" + dddb + "','" + dddh + "','" + ddxh + "','缺单','','','',''," + whsl + ",'','" + ddph + "','" + yjjh + "'";
                        try
                        {
                            if (db.ExecDataBySql1(strSql) > 0)
                            {

                            }
                            else
                            {
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "软件提示");
                            throw ex;
                        }

                        //插入下阶需求数据
                        strSql = "INSERT INTO MRPTD SELECT '" + khbh + "','','','" + dddb + "','" + dddh + "','" + ddxh + "',MD003 下阶品号,MB017,CEILING(" + whsl + " * MD006/MD007) 订单数量 ,0 AS 已交数量,CEILING(" + whsl + "*MD006/MD007)  AS 未交数量,'" + yjjh + "' AS 预计交货日," + (a + 1) + " FROM BOMMD LEFT JOIN INVMB ON MD003=MB001 WHERE MB025='M' AND MD001='" + ddph + "'";
                        //textBox3.Text = strSql;
                        try
                        {
                            if (db.ExecDataBySql1(strSql) > 0)
                            {
                            }
                            else
                            {
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "软件提示");
                            throw ex;

                        }
                    }
                }  //逐行for 循环
                progressBar1.Visible = false;
            }//阶次for循环

            if (textBox10.Text == "")
            {
                textBox10.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "主计划运算成功");
            }
            else
            {
                textBox10.AppendText("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + "主计划运算成功");
            }
        }

        private void button15_Click(object sender, EventArgs e)
        {
            System.Timers.Timer timer = new System.Timers.Timer();
            timer.Enabled = true;
            timer.Interval = 60000;//60秒执行间隔时间,单位为毫秒    
            timer.Start();
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Timer4_Elapsed);
            textBox16.Text = "运行中";
            textBox16.ForeColor = Color.Green;
        }

        private void button14_Click(object sender, EventArgs e)
        {
            textBox16.Text = "等待中";
            textBox16.ForeColor = Color.Red;
        }

        private void Timer4_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {

            int hour = Convert.ToInt16(textBox17.Text);
            int minutes = Convert.ToInt16(textBox18.Text);
            // 得到intHour,intMinute,intSecond，是当前系统时间    
            int intHour = e.SignalTime.Hour;
            int intMinute = e.SignalTime.Minute;
            int intSecond = e.SignalTime.Second;
            // System.Windows.Forms.MessageBox.Show(intSecond.ToString());
            //自己设置hour,minuter,second,到设定的时间就开始执行程序    
            if (intHour == hour && intMinute == minutes & textBox16.Text == "运行中")
            {
                button17.PerformClick();
                Thread.Sleep(5000);
                button13.PerformClick();
            }

        }

        private void button16_Click(object sender, EventArgs e)
        {
            String strSql = "";

            strSql = "SELECT TC004 客户编号,KHDH 客户单号,KHHH 客户行号,TD001 单别,TD002 单号,TD003 序号,RTRIM(TD004) 品号,TD007 仓库,CEILING(TD008) 订单数量 ,CEILING(TD009) 已交数量,CEILING(TD008-TD009) AS 未交数量,(CASE WHEN YJJH='' THEN CONVERT(CHAR(8),GETDATE(),112) ELSE YJJH END) AS 预计交货日,0 AS 已核数量,MB017 主要仓库 FROM MRPTD LEFT JOIN INVMB ON TD004=MB001 LEFT JOIN COPMA ON TC004=MA001 WHERE (TD004 LIKE '" + textBox1.Text + "%' OR '" + textBox1.Text + "'='') AND (TC004 LIKE '" + textBox2.Text + "%' OR '" + textBox2.Text + "'='') AND (MA002 LIKE '" + textBox3.Text + "%' OR '" + textBox3.Text + "'='') AND Level=0";
            //MessageBox.Show(strSql,"提示");
            try
            {
                DataTable dt;
                dataGridView5.DataSource = "";
                dataGridView5.Refresh();

                dt = db.GetDataSet1(strSql, "COPTD").Tables["COPTD"];
                dataGridView5.DataSource = dt;
                this.dataGridView5.Columns["已核数量"].Visible = false;
                this.dataGridView5.Columns["主要仓库"].Visible = false;
                this.dataGridView5.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView5.AllowUserToAddRows = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
        }

        private void dataGridView5_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            
        }

        private void button17_Click(object sender, EventArgs e)
        {
            string strSql = "";
            strSql = "INSERT INTO FHHZ SELECT CONVERT(CHAR(8),GETDATE()-1,112), RTRIM(SFCTA.TA006),CAST(SUM(V_FHMX.FH) AS NUMERIC(16,2)) FROM V_FHMX  Left JOIN SFCTA On V_FHMX.TA001=SFCTA.TA001 and V_FHMX.TA002=SFCTA.TA002 and V_FHMX.TA003=SFCTA.TA003 WHERE NOT EXISTS(SELECT * FROM FHHZ WHERE ZZ01=CONVERT(CHAR(8),GETDATE()-1,112) AND ZZ02=RTRIM(SFCTA.TA006)) GROUP BY SFCTA.TA006";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            strSql = "INSERT INTO FHHZA SELECT CONVERT(CHAR(8),GETDATE()-1,112), RTRIM(SFCTA.TA006) as 区域,CAST(SUM(V_FHMXA.FH) AS NUMERIC(16,2)) as 负荷 FROM V_FHMXA  Left JOIN SFCTA On V_FHMXA.TA001=SFCTA.TA001 and V_FHMXA.TA002=SFCTA.TA002 and V_FHMXA.TA003=SFCTA.TA003 WHERE NOT EXISTS(SELECT * FROM FHHZA WHERE ZZ01=CONVERT(CHAR(8),GETDATE()-1,112) AND ZZ02=RTRIM(SFCTA.TA006)) GROUP BY SFCTA.TA006";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
               //备份记录
            if (textBox10.Text == "")
            {
                textBox10.AppendText(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "负荷备份成功");
            }
            else
            {
                textBox10.AppendText("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + "负荷备份成功");
            }

        }

        private void button20_Click(object sender, EventArgs e)
        {
            System.Timers.Timer timer = new System.Timers.Timer();
            timer.Enabled = true;
            timer.Interval = 60000;//60秒执行间隔时间,单位为毫秒    
            timer.Start();
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Timer5_Elapsed);
            textBox21.Text = "运行中";
            textBox21.ForeColor = Color.Green;
        }

        private void button19_Click(object sender, EventArgs e)
        {
            textBox21.Text = "等待中";
            textBox21.ForeColor = Color.Red;
        }

        private void Timer5_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {

            int hour = Convert.ToInt16(textBox19.Text);
            int minutes = Convert.ToInt16(textBox20.Text);
            // 得到intHour,intMinute,intSecond，是当前系统时间    
            int intHour = e.SignalTime.Hour;
            int intMinute = e.SignalTime.Minute;
            int intSecond = e.SignalTime.Second;
            // System.Windows.Forms.MessageBox.Show(intSecond.ToString());
            //自己设置hour,minuter,second,到设定的时间就开始执行程序    
            if (intHour == hour && intMinute == minutes & textBox21.Text == "运行中")
            {
                button21.PerformClick();
            }

        }

        private void button21_Click(object sender, EventArgs e)
        {
            String strSql = "";
            strSql = "SELECT TE023 生产日期,TD003 报工日期,ISNULL(SFCTD.UDF01,'') 班次,RTRIM(TE004) 员工编号,RTRIM(MV002) 员工姓名,RTRIM(MV020) 微信账号,TE006 工单单别,TE007 工单单号,RTRIM(TE017) 品号,RTRIM(TE018) 品名,RTRIM(TE019) 规格,RTRIM(MW002)+'('+RTRIM(SUBSTRING(MG007,1,255))+')' 工艺说明,RTRIM(A.TE015) 备注,CAST(TE011 AS REAL) 数量,CEILING(A.UDF51) 料废,CEILING(A.UDF52) 工废,CEILING(A.UDF53) 不良,CAST(MGD09 AS real) 计件工时,TE001 单别,TE002 单号,TE003 序号 FROM SFCTE A LEFT JOIN SFCTD ON TE001=TD001 AND TE002=TD002 LEFT JOIN INVMB ON TE017=MB001 LEFT JOIN BOMMG ON MB010=MG001 AND MB011=MG002 AND TE008=MG003 AND A.UDF08=MG006 LEFT JOIN CMSMV ON TE004=MV001 LEFT JOIN CMSMW ON TE009=MW001 WHERE TE001='D410' AND TE023=CONVERT(CHAR(8),GETDATE()-1,112) AND TE011>0 AND MV020<>'' ORDER BY A.TE023,A.TE004";
            try
            {
                dataGridView6.DataSource = null;
                this.dataGridView6.Invalidate();
                dataGridView6.DataSource = db.GetDataSet1(strSql, "SFCTE").Tables["SFCTE"];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
         
                  //开始推送工资信息
            try
            {
                //获取发送消息的token
                var client = new RestClient("https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=ww9f0e8d7105c6b0e7&corpsecret=t0lUOU_MRI3MkZIt5VgVVLbc_qbV--CEzGXgu9pmDSU");
                client.Timeout = -1;
                var request = new RestRequest(Method.GET);
                IRestResponse response = client.Execute(request);
                Logininfor lg = JsonConvert.DeserializeObject<Logininfor>(response.Content);
                String tokenstring = lg.access_token.ToString();

                for (int i = 0; i <= this.dataGridView6.Rows.Count - 1; i++)
                {
                    String tjry = (dataGridView6.Rows[i].Cells["微信账号"].Value == null) ? "" : dataGridView6.Rows[i].Cells["微信账号"].Value.ToString();
                    String ygxm = (dataGridView6.Rows[i].Cells["员工姓名"].Value == null) ? "" : dataGridView6.Rows[i].Cells["员工姓名"].Value.ToString();
                    String scrq = (dataGridView6.Rows[i].Cells["生产日期"].Value == null) ? "" : dataGridView6.Rows[i].Cells["生产日期"].Value.ToString();
                    String cppm = (dataGridView6.Rows[i].Cells["品名"].Value == null) ? "" : dataGridView6.Rows[i].Cells["品名"].Value.ToString();
                    String cpgg = (dataGridView6.Rows[i].Cells["规格"].Value == null) ? "" : dataGridView6.Rows[i].Cells["规格"].Value.ToString();
                    String gysm = (dataGridView6.Rows[i].Cells["工艺说明"].Value == null) ? "" : dataGridView6.Rows[i].Cells["工艺说明"].Value.ToString();
                    String scsl = (dataGridView6.Rows[i].Cells["数量"].Value == null) ? "" : dataGridView6.Rows[i].Cells["数量"].Value.ToString();
                    //推送消息
                    client = new RestClient("https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=" + tokenstring + "");
                    client.Timeout = -1;
                    request = new RestRequest(Method.POST);
                    request.AddHeader("Content-Type", "text/plain");
                    request.AddParameter("text/plain", "{\r\n   \"touser\" : \"" + tjry + "\",\r\n   \"msgtype\" : \"text\",\r\n   \"agentid\" : 1000002,\r\n   \"text\" : {\r\n       \"content\" : \"" + ygxm + "同仁：\r\n    您于" + scrq + "完成以下生产任务：\r\n 产品品名为：" + cppm.Trim() + "；规格为：" + cpgg.Trim() + "；\r\n 加工工艺为:" + gysm.Trim() + "；\r\n 生产数量为：" + scsl + "；\r\n 请您认真核对，如有差错请及时联系报工人员！\"\r\n   },\r\n   \"safe\":0\r\n}", ParameterType.RequestBody);
                    response = client.Execute(request);

                }
                //推送给财务消息和管理员
                client = new RestClient("https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=" + tokenstring + "");
                client.Timeout = -1;
                request = new RestRequest(Method.POST);
                request.AddHeader("Content-Type", "text/plain");
                request.AddParameter("text/plain", "{\r\n   \"touser\" : \"Laixuanbin|13566914062|15067986744|13666827511\",\r\n   \"msgtype\" : \"text\",\r\n   \"agentid\" : 1000002,\r\n   \"text\" : {\r\n       \"content\" : \"昨日工资信息已经成功推送！\"\r\n   },\r\n   \"safe\":0\r\n}", ParameterType.RequestBody);
                response = client.Execute(request);
            }
            catch
            {

            }
        }
    }
}
       
  
